СУБД, или Системы управления базами данных

Здесь все конспекты из разных мест, которые я затем интегрирую в один большой

Включение хайлайтинга кода SQL

[2]:

!jupyter nbextension enable notebook_magiclight-master/index

Enabling notebook extension notebook_magiclight-master/index...
      - Validating: ok

Источники:

  1. Stepik: Введение в базы данных

  2. Книга: Распределенные данные. Алгоритмы работы современных систем хранения информации. Алекс Петров. 2021

  3. Введение в системы баз данных. К. Дж. Дейт. 2005

  4. Лекции ПАД (ЭФ НГУ)

  5. Нетология: Основы SQL

  6. Изучаем SQL. Алан Болье. 2021

Мини-источники:

Данные: - [ ] [Remote Data Access](https://pandas-datareader.readthedocs.io/en/latest/remote_data.html) - [ ] [7.1. Toy datasets](https://scikit-learn.org/stable/datasets/toy_dataset.html) - [ ] [datasets 2.6.1](https://pypi.org/project/datasets/) - [ ] [jupyter-datatables 0.3.9](https://pypi.org/project/jupyter-datatables/) - [ ] []() - [ ] []() - [ ] []() - [ ] []() - [ ] []() - [ ] []() - [ ] []()

Введение в СУБД

Основные определения

Система баз данных - компьютеризированная система хранения однотипных записей

База данных - хранилище или контейнер для некоторого набора файлов данных, занесенных в компьютер.

Пользователям этой системы предоставляется возможность выполнять (или передавать системе запросы на выполнение) множество различных операций над такими файлами, например: * добавлять новые пустые файлы в базу данных; * вставлять новые данные в существующие файлы; * получать данные из существующих файлов; * удалять данные из существующих файлов; * изменять данные в существующих файлах; * удалять существующие файлы из базы данных.

ПРИМЕР: БД CELLAR - Винный погреб. В табл. 1.1 показан пример выполнения операции выборки и данные, возвращенные этой операцией.

ПРИМЕР: Телефонная книга - база данных имен, номеров телефонов и адресов всех людей, живущх в определенном районе.

Телефонная книга как БД обладает рядом недостатков, присущим всем ручным системам хранения данных: * Поиск номаера человека может заниамть много времени * ТК индексируется только по ФИО, и поиск людей по определенному адресу очень осложнен * С момента печати ТК, инфорамация в ней устаревает, т.к. люди переезжают, меняю номер телефона, ФИО.

ПРИМЕР: Истории болезни, хранящиеся в картотеке поликлиники

!!!!!!!!!!!!!!!!!!!!!: Операции над базами данных, имена файлов и т.п. в этой книге для наглядности пишутся прописными буквами, хотя на практике часто удобнее использовать строчные. В большинстве СУБД допускаются оба варианта

imadsfdsfdsfsdfsdfdsffgdfge.png

Выборка:

SELECT WINE, BIN#, PRODUCER FROM CELLAR WHERE READY = 2004;

В языке SQL компьютерные файлы, такие как CELLAR в табл. 11, называются таб лицами (по очевидным причинам). Строки (row) подобных таблиц соответствуют записям файла, а столбцы (column) можно рассматривать как поля этих записей. В дальнейшем термины запись и поле будут использоваться тогда, когда речь будет идти о базах данных вообще (в основном, в первых двух главах). Термины таблица, строка и столбец будут использоваться при рассмотрении реляционных систем

Столбец BIN# является первичным ключом (primary key) таблицы CELLAR (подразумевается, что любые две строки этой таблицы никогда не будут содержать одно и то же значение поля BIN#).

Пояснения SQL

Кодд разработал реляционную модель БД, а также предложил язык, названный DSL/Alpha, для управления данными в реляционных таблицах. Вскоре после выхода статьи IBM поручила разработать прототип БД, основанный на идеях кода. Им стала упрощенная версия DSL/Alpha - SQUARE. Его усовершентсвованная версия была названа SEQUEL, которая в конечнос счете и была сокращена до SQL.

Язык SQL был первоначально разработан компанией IBM, а в настоящее время поддерживается большинством коммерческих СУБД, представленных на рынке, и является официальным стандартом языка для работы с реляционными базами данных.

Хотя SQL и начинался как язык работы с реляционными БД, но он опстепенно превратился в универсальный интструмент работы с БД.

Название SQL вначале было аббревиатурой, образованной от Structured Query Language (язык структурированных запросов), и его было принято произносить как “сиквел”. Сейчас, когда язык стал стандартом, SQL— это уже не аббревиатура, а обычное название, которое произносится как “эс-кью-эл”. В дальнейшем в этой книге предполагается именно такой вариант произношения.

Вставка новых данных:

INSERT INTO CELLAR (BIN#, WINE, PRODUCER, YEAR, BOTTLES, READY) VALUES (53, 'Pinot Noir', 'Saintsbury', 2001 , 6, 2005);

Удаление существующих данных:

DELETE FROM CELLAR WHERE BIN# = 2;

Модификация существующих данных:

UPDATE CELLAR SET BOTTLES = 4 WHERE BIN# = 3;

Данные и информация

Следует отметить, что термины данные и информация трактуются в этой книге как синонимы. Некоторые авторы предпочитают различать эти два понятия. * Понятие данные используется для ссылки на значения, которые реально сохранены в базе данных * Понятие информация — для указания на то, что означают эти данные с точки зрения пользователя.

Разница, безусловно, существенная, но предпочтительнее сделать ее более определенной там, где это уместно, вместо того, чтобы полагаться на различные толкования двух по сути одинаковых терминов.

Система баз данных

Четыре главынх компонента: 1. Данные 2. Аппаратное обеспечение 3. Программное обеспечение 4. Пользователи

izdfxtgcyhfdghmage.png

1. Данные

На больших вычислительных машинах применяются в основном многопользовательские системы (“большие системы”), а на малых компьютерах, как правило, — однопользовательские системы (“малые системы”).

Однопользовательская система (single-user system) — это система, в которой к базе данных может получить доступ одновременно только один пользователь

многопользовательская система (multi-user system) — это такая система, в которой к базе данных могут получить доступ сразу несколько пользователей

В общем случае данные в базе данных: 1. Интегрированными (Преимущество при работе на малом оборудовании) 2. Разделяемыми

Под понятием интеграции данных подразумевается возможность представить базу данных как объединение нескольких отдельных файлов данных, полностью или частично исключающее избыточность хранения информации.

Например, база данных может содержать файл EMPLOYEE, включающий имена сотрудников, адреса, отделы, данные о зарплате и т.д., и файл ENROLLMENT, содержащий сведения о регистрации сотрудников на курсах обучения (рис. 1.4).

Допустим, что для контроля процесса обучения необходимо знать отдел каждого зачисленного на курсы сотрудника. Совершенно очевидно, что нет необходимости включать такую информацию в файл ENROLLMENT, поскольку ее всегда можно получить из файла EMPLOYEE.

imaSFDFSDFSDFDFSFDSFSDge.png

Под понятием разделяемых данных подразумевается возможность использования несколькими различными пользователями отдельных элементов, хранимых в базе данных.

Имеется в виду, что каждый из пользователей сможет получить доступ к одним и тем же данным, возможно, даже одновременно (параллельный доступ). Такое разделение данных, с параллельным или последовательным доступом, частично является следствием того факта, что база данных имеет интегрированную структуру.

В примере, приведенном на рис. 1.4, информация об отделе в файле EMPLOYEE может совместно использоваться сотрудниками отдела кадров и отдела обучения. (Если база данных не является разделяемой, то ее иногда называют личной базой или базой данных специального назначения.)

2. Аппартаное обеспечение

К аппаратному обеспечению системы относится следующее: * тома вторичной (внешней) памяти (обычно это магнитные диски), используемые для хранения информации, а также соответствующие устройства ввода—вывода (дисководы и т.п.), контроллеры устройств, каналы ввода—вывода и т.д.; * аппаратный процессор (или процессоры) вместе с оперативной (первичной) памятью, предназначенные для поддержки работы программного обеспечения системы баз данных (подробности приведены в следующем подразделе).

3. Программное обеспечение

Между собственно физической базой данных (т.е. данными, которые реально хранятся на компьютере) и пользователями системы располагается уровень программного обеспечения, который можно называть по-разному: 1. диспетчер базы данных (database manager) 2. сервер базы данных (database server) 3. система управления базами данных, СУБД (DataBase Management System — DBMS).

  • Все запросы пользователей на получение доступа к базе данных обрабатываются СУБД

  • Все имеющиеся средства добавления файлов (или таблиц), выборки и обновления данных в этих файлах или таблицах также предоставляет СУБД.

Основная задача СУБД — дать пользователю базы данных возможность работать с ней, не вникая во все подробности работы на уровне аппаратного обеспечения.

(Пользователь СУБД более отстранен от этих подробностей, чем прикладной программист, применяющий языковую среду программирования.)

СУБД позволяет конечному пользователю рассматривать базу данных как объект более высокого уровня по сравнению с аппаратным обеспечением, а также предоставляет в его распоряжение набор операций, выражаемых в терминах языка высокого уровня

СУБД— это наиболее важный, но не единственный программный компонент системы.

В числе других компонентов можно назвать утилиты, средства разработки приложений, средства проектирования, генераторы отчетов и диспетчер транзакций (transaction manager), или диспетчер обработки транзакций (transaction processing monitor — ТР monitor)

!!!!!!!!!! Следует отметить, что термин база данных часто используется даже тогда, когда на самом деле подразумевается СУБД (в одном из уже упомянутых толкований). Вот типичный пример: “База данных изготовителя X превосходит по производительности базу данных изготовителя К в два раза”. Такое небрежное обращение с терминами предосудительно; тем не менее, оно очень широко распространено.

(Проблема, естественно, заключается в том, что если СУБД называют базой данных, как же тогда называть саму базу данных?) Читатель, будь внимателен!

4. Пользователи

Пользователей можно разделить на три большие и отчасти перекрывающиеся группы: 1. ПРИКЛАДНЫЕ ПРОГРАММИСТЫ - которые отвечают за написание прикладных программ, использующих базу данных.

  • Для этих целей применимы такие языки, как COBOL, PL/I, C++, Java или какой-нибудь высокоуровневый язык четвертого поколения.

  • Прикладные программы получают доступ к базе данных посредством выдачи соответствующего запроса к СУБД (обычно это некоторый оператор SQL). Подобные программы могут быть простыми пакетными приложениями или же интерактивными приложениями, предназначенными для поддержки работы конечных пользователей.

  • В последнем случае они предоставляют пользователям непосредственный оперативный доступ к базе данных через рабочую станцию, терминал или персональный компьютер.

  1. КОНЕЧНЫЕ ПОЛЬЗОВАТЕЛИ - которые работают с системой баз данных в интерактивном режиме.

  • Конечный пользователь может получать доступ к базе данных, применяя одно из интерактивных приложений, упомянутых выше, или же интерфейс, интегрированный в программное обеспечение самой СУБД.

  • Безусловно, подобный интерфейс также поддерживается интерактивными приложениями, но эти приложения не создаются пользователями-программистами, а являются встроенными в СУБД.

  • Большинство СУБД включает по крайней мере одно такое встроенное приложение, а именно — процессор языка запросов, позволяющий пользователю в диалоговом режиме вводить запросы к базе данных (их часто иначе называют предложениями или командами), например, с операторами SELECT или INSERT.

  • Язык SQL представляет собой типичный пример языка запросов к базе данных.

  • (Общепринятый термин язык запросов не совсем точно отражает рассматриваемое понятие, поскольку слово запрос подразумевает лишь выборку информации, в то время как с помощью этого языка выполняются также операции обновления, вставки, удаления и др.)

  • Кроме языка запросов, в большинстве систем дополнительно предоставляются специализированные встроенные интерфейсы, в которых пользователь в явном виде не использует предложения, или команды с такими операторами, как SELECT и INSERT. Работа с базой данных осуществляется за счет выбора пользователем необходимых элементов меню или заполнения требуемых полей в предоставленных формах. Такие некомандные интерфейсы, основанные на меню и формах, облегчают работу с базами данных для тех, кто не имеет опыта работы с информационными технологиями

  • Командный интерфейс, т.е. язык запросов, напротив, требует некоторого профессионального опыта работы с ИТ.

  • Однако командный интерфейс более гибок, чем некомандный, к тому же языки запросов обычно включают определенные функции, отсутствующие в интерфейсах, основанных на использовании меню или форм.

  1. АДМИНИСТРАТОРЫ БАЗЫ ДАННЫХ (АБД)

Общее определение базы данных

Перманентные данные (постоянное хранимые) - данные , которые отличаются от других, более изменчивых данных, таких как промежуточные результаты, входные и выходные данные, управляющие операторы, рабочие очереди, программные управляющие блоки и вообще все данные, временные (transient) по своей сути

Можно утверждать, что данные в базе являются перманентными, поскольку после того как они были приняты средствами СУБД для помещения в базу, их последующее удаление возможно лишь при использовании соответствующего явного запроса к базе данных, но не в результате какого-либо побочного эффекта от выполнения некоторой программы.

Подобный взгляд на понятие перманентности позволяет точнее определить терминбаза данных.

База данных - некоторый набор перманентных (постоянно хранимых) данных, используемых прикладными программными системами какого-либо предприятия.

Ниже приведено несколько примеров ПРЕДПРИЯТИЙ: 1. Промышленная компания. 2. Банк. 3. Больница. 4. Университет. 5. Министерство

Среди перманентных данных упомянутых предприятий обычно встречаются данные, перечисленные ниже: 1. Данные о продукции. 2. Бухгалтерские данные. 3. Данные о пациентах. 4. Данные о студентах. 5. Данные о планируемой деятельности.

Сущности и связи

диаграммой “сущность—связь” (сокращенно ER-диаграммой).

imaSDFGDSADFWECFFCDCDge.png

Рассмотрим более подробно пример некоторой промышленной компании (допустим, она имеет название KnowWare, Inc.).

Обычно подобному предприятию требуется записывать информацию об имеющихся проектах (Projects), используемых в этих проектах деталях (Parts), поставщиках (Suppliers) деталей, складах (Warehouses), на которых хранятся детали, служащих (Employees), работающих над проектами и т.д.

Проекты, детали, поставщики и т.д. представляют собой основные сущности (entity), о которых компании KnowWare, Inc. необходимо хранить информацию.

В теории баз данных термин сущность обычно используется для обозначения любого различимого объекта, который может быть представлен в базе данных

Кроме этих основных сущностей (в данном примере это поставщики, детали и т.д.), имеются еще и связи (relationship) между ними, которые объединяют эти основные сущностипредставлены ромбами с соединительными линиями.

Например, между поставщиками и деталями существует связь SP (сокращение от Shipments/Parts): каждый поставщик поставляет определенные детали, и наоборот, каждая деталь поставляется определенными поставщиками.

(Точнее, каждый поставщик поставляет определенные виды деталей, и каждый вид деталей поставляется определенными поставщиками.)

Аналогично, детали используются в проектах, а для реализации проектов требуются детали (связь PJ — сокращение от Parts/proJects); детали хранятся на складах, а склады хранят детали (связь WP — сокращение от Warehouses/Parts) и т.д.

Обратите внимание, что эти связи — бинарные (или двухсторонние), т.е. их можно прослеживать в любом направлении. В частности, используя связь SP между поставщиками и деталями, можно ответить на следующие вопросы: * задан поставщик, и требуется определить поставляемые им детали; * задана деталь, и необходимо найти поставщиков, предоставляющих такую деталь.

Хотя большинство связей на этой диаграмме объединяют два типа сущностей (т.е. они являются бинарными связями), это вовсе не означает, что все связи должны быть бинарными.

В примере есть одна связь (SPJ), охватывающая три типа сущностей (Suppliers, Parts и Projects). Это пример тернарной (трехсторонней) связи.

Интерпретация данной связи такова: определенные поставщики поставляют определенные детали для определенных проектов. Обратите особое внимание на то, что в общем случае такая тернарная связь (“поставщики поставляют детали для проектов”) не эквивалентна простой комбинации из трех бинарных связей:

“поставщики поставляют детали”, “детали используются в проектах” и “проекты снабжаются поставщиками”. В качестве примера рассмотрим приведенные ниже утверждения.

а) “Смит поставляет разводные гаечные ключи для Манхэттенского проекта”. Оно содержит больше информации, чем следующие три отдельных утверждения, которые относятся к той же теме.,

б) “Смит поставляет разводные гаечные ключи”.

в) “Разводные гаечные ключи используются в Манхэттенском проекте”.

г) “Манхэттенский проект снабжается Смитом”.

Зная только утверждения б, в и г, мы не сможем доказать справедливость утверждения а

  1. Точнее, зная, что справедливы утверждения б, в и г, мы можем лишь сделать заключение, что Смит поставляет разводные гаечные ключи для какого-то проекта (скажем, проекта Jz), что какой-то поставщик (скажем, поставщик Sx) поставляет разводные гаечные ключи для Манхэттенского проекта и что Смит поставляет какую-то деталь (скажем, деталь PY) для Манхэттенского проекта. Однако мы не можем точно утверждать, что поставщик Sx — это Смит, деталь PY — это разводной гаечный ключ, а проект Jz — это Манхэттенский проект. Ложные выводы, сделанные на основании неполной информации, называются дефектом соединения (connection trap).

  1. На схеме также есть одна связь (РР), которая связывает один тип сущности (Parts) с самим собой. Эта связь означает, что одни детали содержат другие дета ли как собственные компоненты (так называемая связь спецификации материалов, или связь “деталь—узел”). Например, винт— это компонент шарнира, который тоже рассматривается как деталь и, в свою очередь, может быть компонентом ка кой-либо более сложной детали, например колпака. Обратите внимание, что эта связь также бинарная; просто она связывает две сущности совпадающего типа (в данном случае Parts).

  1. Вообще говоря, для заданного набора типов сущностей может существовать любое количество связей. В представленной на рис. 1.5 диаграмме присутствуют две раз личные связи между сущностями Projects и Employees: первая (EJ) представ ляет тот факт, что служащие заняты в проектах, а вторая (MJ) — что служащие управляют проектами.

Связь можно понимать как особого типа. Если сущность определена как “нечто, о чем необходимо хранить информацию”, то понятие связи вполне подходит под такое определение.

Например, связь “деталь Р4 находится на складе W8” — это сущность, о которой может потребоваться записать некоторую информацию, например, зафиксировать в базе данных количество указанных деталей. Благодаря тому, что мы не проводим ненужных различий между сущностями и связями, достигаются определенные преимущества (их описание выходит за рамки настоящей главы). Поэтому в данной книге связи будут рассматриваться как особый вид сущности.

Свойства

сущность — это то, о чем необходимо хранить информацию.

Отсюда следует, что сущности (а значит, и связи) имеют некоторые свойства (properties), соответствующие тем данным о них, которые необходимо хранить в базе.

Например, поставщики имеют определенное место расположения, детали характеризуются весом, проекты — очередностью выполнения, закрепление служащих за проектами имеет начальную дату и т.д.

Например, в базе данных может быть таблица s, представляющая тип сущности “поставщики”, а в этой таблице может присутствовать тип поля CITY (город), представляющий свойство “место расположения”.

Свойства: простые (1 уровень) и сложные (более одного уровня)

Данные и модели данных

Высказыванием в логике называется такое утверждение, которое может быть недвусмысленно определено как истинное или ложное.

Данные - это факты

База данных - это множество истинных высказываний

Итак, уже было отмечено, что продукты на основе языка SQL заняли доминирующее положение на рынке. Одной из причин этого является то, что они основаны на использовании формальной теории, называемой реляционной моделью данных, а эта теория, в свою очередь, поддерживает указанную выше интерпретацию данных и баз данных весьма просто (фактически почти тривиально).

Конкретнее, реляционная модель характеризуется описанными ниже особенностями:

  1. Данные представлены посредством строк в таблицах3 , и эти строки могут быть не посредственно интерпретированы как истинные высказывания. Например, строку с номером ячейки погреба (поле BIN#), равным 72 (см. табл. 1.1), можно очевидным образом интерпретировать как следующее истинное высказывание:

"В ячейке 72 находятся две бутылки вина Zinfandel, выпущенные компанией Rafanelli в 1999 году, которые будут готовы к употреблению в 2007 году".

  1. Для обработки строк данных предоставляются операторы, которые напрямую под держивают процесс логического вывода дополнительных истинных высказываний из существующих высказываний. Например, реляционная операция проекции (раздел 1.6) позволяет получить из приведенного выше истинного высказывания, помимо прочих истинных высказываний, и такое:

"Некоторые бутылки вина Zinfandel будут готовы к употреблению в 2007 году".

  1. Модель данных — это абстрактное, самодостаточное, логическое определение объектов, операторов и прочих элементов, в совокупности составляющих абстрактную машину доступа к данным, с которой взаимодействует пользователь.

Упомянутые объекты позволяют моделировать структуру данных, а операторы —поведение данных.

  1. модель данных (во втором смысле) представляет собой модель перманентных данных некоторого конкретного предприятия (например, промышленной компании KnowWare, Inc., упоминаемой выше в этом разделе).

  • Модель данных в первом значении подобна языку программирования (причем достаточно абстрактному), конструкции которого могут быть использованы для решения широкого круга конкретных задач, но который сам по себе не имеет четкой связи с какой-либо из этих конкретных задач.

  • Модель данных во втором значении подобна конкретной программе, написанной на таком языке. Иначе говоря, модель данных во втором значении использует средства, предоставляемые некоторой моделью (рассматриваемой в первом значении) и применяет их для решения конкретной проблемы. Ее можно рассматривать как некоторое конкретное приложение некоторой модели в первом значении.

Реализация (implementation) заданной модели данных — это физическое воплощение на реальной машине компонентов абстрактной машины, которые в совокупности составляют эту модель

!!!!!!! т.е. разница между логическим и физическим определением данных

Назначение баз данных

Преимущества системы с базой данных по сравнению с традиционным “бумажным” методом ведения учета для этих примеров вполне очевидны. Отметим некоторые из них, которые описаны ниже:

■ Компактность. Нет необходимости в создании и ведении, возможно, весьма объемистых бумажных картотек.

■ Быстродействие. Компьютер может выбирать и обновлять данные гораздо быстрее человека. В частности, с его помощью можно быстро получать ответы на произ вольные вопросы, возникающие в процессе работы (например, “Какого вина у нас сейчас больше — Zinfandel или Pinot Noir?”), не затрачивая времени на визуаль ный осмотр или поиск вручную.

■ Низкие трудозатраты. Отпадает необходимость в утомительной работе над картотекой вручную. Механическую работу машины всегда выполняют лучше.

■ Актуальность. В случае необходимости под рукой в любой момент имеется точная, свежая информация.

■ Защита. Данные могут быть лучше защищены от случайной потери и несанкционированного доступа.

Администрирование данных и базы данных

администратор данных (АД)- человек, который несет основную ответственность за данные предприятия.

В его обязанности входит принятие решений о том, какие данные необходимо вносить в базу данных в первую очередь, а также выработка требований по сопровождению и обработке данных после их занесения в базу данных. Примером подобных требований может служить распоряжение о том, кто и при каких обстоятельствах имеет право выполнять конкретные операции над теми или иными данными. Другими словами, администратор данных должен обеспечивать защиту данных (подробнее об этом речь пойдет ниже).

администратор базы данных (АБД) - Технический специалист, ответственный за реализацию решений администратора данных

Работа АБД заключается в создании самих баз данных и организации технического контроля, необходимого для осуществления решений, принятых администратором данных. АБД несет также ответственность за обеспечение необходимого быстродействия системы и ее техническое обслуживание

Транзакция (transaction) — это логическая единица работы (точнее, логическая единица работы базы данных), обычно включающая несколько операций базы данных (в частности, несколько операций модификации данных).

Стандартный пример — перевод некоторой суммы денег со счета А на счет В. Очевидно, что в данном случае необходимы два изменения: списание некоторой суммы со счета А и зачисление ее на счет В. Если пользователь укажет, что оба изменения входят в одну и ту же транзакцию, то система сможет реально гарантировать, что либо будут выполнены оба эти изменения, либо не будет выполнено ни одно из них, если до завершения процесса внесения изменений в системе произойдет сбой (скажем, из-за перерыва в подаче электроэнергии).

свойство неразрывности (atomicity) транзакций

Для этого администратор данных определяет (а администратор базы данных реализует) ограничения целостности (integrity constraints), которые будут применяться при любой попытке внести какие-либо изменения в соответствующие данные.

Противоречие между двумя записями, представляющими один “факт”, является примером утраты целостности данных (см. обсуждение этого вопроса выше в данном разделе).

граничения защиты (security constraints), или правила, которые будут контролироваться при любой попытке доступа к конфиденциальным данным. Можно установить азличные правила для разных типов доступа (выборка, вставка, удаление и т.д.) к каждому из элементов информации в базе данных. Однако следует отметить, что при отсутствии таких правил безопасность данных подвергается большему риску, чем в обычной (разобщенной) файловой системе. Следовательно, централизованная природа системы баз данных в определенном смысле требует также наличия надежной системы защиты.

НЕЗАВИСИМОСТЬ ОТ ДАННЫХ

Независимость от данных может быть реализована на двух уровнях: физическом и логическом. Однако на данном этапе нас интересует только физическая независимость. Поэтому неуточненный термин независимость от данных мы пока будем понимать лишь как физическую независимость от данных.

Проще всего разобраться в понятии независимости от данных на примере той ситуации, когда независимость от данных отсутствует. Приложения, реализованные в старых системах (дореляционные, или созданные до появления систем баз данных), в той или иной мере зависимы от данных. Это означает, что способ организации данных во вторичной памяти и способ доступа к ним диктуются требованиями приложения. Более того, сведения об организации данных и способе доступа к ним встроены в саму логику и программный код приложения

Например, предположим, что в некотором приложении используется файл EMPLOYEE (см. рис. 1.4). Исходя из соображений производительности решено, что этот файл необходимо проиндексировать по полю “имя служащего” (см. приложение Г). В старых системах в этом приложении учитывалось бы, что такой индекс существует и что последовательность записей в файле определена этим индексом. На основе таких сведений была бы построена вся внутренняя структура приложения. В частности, избранный способ реализации процедур доступа и обработки исключительных ситуаций в значительной степени зависел бы от особенностей интерфейса, предоставляемого программами управления данными.

Приложения, подобные описанному в этом примере, называются зависимыми от данных, так как невозможно изменить физическое представление (т.е. способ физического размещения данных во вторичной памяти) или метод доступа (т.е. конкретный способ доступа к данным), не изменив самого приложения (возможно, весьма радикально). Например, невозможно заменить индексированный файл в нашем примере хэшированным файлом, не внеся в приложение значительных изменений. Более того, изменению в подобных случаях подлежат те части приложения, которые взаимодействуют с программами управления данными. Трудности, возникающие при этом, не имеют никакого отношения к проблеме, для решения которой было написано данное приложени

Однако для системы баз данных крайне нежелательно, чтобы приложение зависело от данных, и на то есть по меньшей мере две причины, описанные ниже.

  1. Для разных приложений требуются разные представления одних и тех же данных. Например, предположим, что до перехода к интегрированной базе данных пред приятие имело два приложения, А и В. Каждое из них работало с собственным файлом, содержащим поле “остаток средств на счете заказчика”. Предположим также, что приложение А записывает значение этого поля в десятичном формате, а приложение В — в двоичном. Эти два файла все еще можно интегрировать, а су ществующую избыточность устранить, если в СУБД есть возможность выполнить все необходимые преобразования между форматом представления данных (формат представления может быть десятичным, двоичным или любым другим) и форма том, необходимым для приложения. Например, если принято решение хранить значения этого поля в десятичном формате, то каждое обращение к приложению В потребует прямого или обратного преобразования значений из десятичного фор мата в двоичный. Это довольно простой пример различий, которые могут существовать в системе баз данных между формой представления данных в приложении и формой их физического хранения. Многие другие возможные различия будут рассмотрены ниже.

  1. Администратор базы данных должен иметь определенные возможности (завися щие от применяемой СУБД) по изменению физического представления или метода доступа к данным в случае изменения требований, причем без необходи мости модифицировать существующие приложения. Например, к базе данных могут быть добавлены новые виды данных, на предприятии могут быть приняты новые стандарты, могут быть изменены приоритеты приложений (а следовательно, и связанные с ними требования к производительности), могут появиться новые типы запоминающих устройств и т.д. Если приложения зависят от данных, то подобные изменения потребуют внесения корректировок в программы, а значит, дополнительных трудозатрат программистов, которые можно было бы направить на создание новых приложений. До сих пор подобные проблемы не являются исключением. И сегодня случается, что значительная часть рабочего времени программистов тратится на подобную работу (достаточно вспомнить хотя бы проблему 2000-го года!), а это, конечно, бесполезная трата дефицитных

Независимость от данных можно определить как невосприимчивость приложений к изменениям в физическом представлении данных и в методах доступа к ним, а это означает, что рассматриваемые приложения не зависят от любых конкретных способов физического представления информации или выбранных методов доступа к ним

Хранимое поле — это наименьшая единица хранимых данных. Типичная база данных содержит множество экземпляров (occurence, или instance) каждого из нескольких описанных в ней типов хранимых полей.

Например, база данных, содержащая информацию о деталях, может включать тип хранимого поля с име нем “номер детали” и для каждого описанного в базе данных вида детали (винта, шарнира, колпака и т.д.) будет существовать отдельный экземпляр этого храни мого поля.

Хранимая запись — это набор взаимосвязанных хранимых полей. И снова мы раз личаем для них тип и экземпляр. В данном случае экземпляр хранимой записи со стоит из группы связанных экземпляров хранимых полей. Например, экземпляр хранимой записи в базе данных деталей состоит из экземпляров каждого из сле дующих хранимых полей: “номер детали”, “название детали”, “цвет детали” и “вес детали”. Мы говорим, что база данных содержит множество экземпляров храни мой записи типа “деталь” (опять же, по одному экземпляру для каждой конкрет ной детали).

хранимый файл — это набор всех существующих в настоящий момент эк земпляров хранимых записей одного и того же типа. (Для упрощения предполага ется, что любой заданный хранимый файл может содержать хранимые записи только одного типа. Это упрощение не окажет существенного влияния на последующие рассуждения.)

imaASDDFGNBDFDFSGHJKHMNge.png

В современных системах, отличных от баз данных, логическая (с точки зрения разработчика приложения) запись обычно совпадает с соответствующей хранимой записью. Как было показано выше, в базах данных это вовсе не обязательно, поскольку в любой момент может потребоваться внести изменения в структуру хранения данных (т.е. в хранимые поля, записи, файлы), в то время как структура данных с точки зрения приложения должна остаться неизменной. Например, поле SALARY В файле EMPLOYEE ДЛЯ ЭКОНОМИИ памяти может быть сохранено в двоичном формате, а в приложении, написанном на языке COBOL, это поле может рассматриваться в качестве символьной строки. В дальнейшем по каким-то причинам может понадобиться изменить двоичную форму представления этого поля на десятичную, сохранив для приложения возможность обрабатывать поле в символьном формате.

■ Представление числовых данных Числовое поле может храниться во внутренней арифметической форме (например, в упакованном десятичном формате) или в виде символьной строки. В каждом случае АБД должен определить, следует ли применять числа с фиксированной или плавающей точкой, выбрать подходящее основание системы счисления (например, двоичную или десятичную систему), точность (количество цифр во внутреннем представлении числа), а если это число с фиксированной точкой, то определить величину дробной части (количество цифр после точки, разделяющей целую и дробную части числа). Каждый из этих параметров может быть изменен в целях повышения производительности, при введении нового стандарта или по некоторым другим причинам. ■ Представление символьных данных Поле в формате символьной строки может храниться с использованием любого из существующих наборов кодировок символов (например ASCII, EBCDIC, Unicode). ■ Единицы измерения для числовых данных Единицы измерения числовых полей могут быть изменены, например, дюймы могут быть преобразованы в сантиметры в ходе внедрения метрической системы единиц. ■ Кодирование данных В некоторых ситуациях может понадобиться представлять хранимые данные в виде кодированных значений. В частности, поле “цвет детали”, которое представлено в приложении как символьная строка (“красный”, “голубой”, “зеленый”), может храниться в виде десятичной цифры в соответствии с некоторой таблицей перекодировки, например 1 = “красный”, 2 = “голубой” и т.д. ■ Материализация данных Используемое приложением логическое поле обычно действительно соответствует некоторому определенному хранимому полю (хотя, как было показано выше, могут существовать различия в типе данных, применяемой кодировке и т.д.). В этом случае процесс материализации (materialization), т.е. процесс построения экземпляра логического поля из соответствующего экземпляра хранимого поля и его передачи приложению, называется прямым. Однако иногда логическое поле может не иметь соответствующего эквивалентного хранимого поля, а его значение будет материализоваться с помощью некоторых вычислений, выполняемых над набором из нескольких экземпляров хранимых полей. Например, значение логического поля “общее количество” можно определить путем суммирования нескольких хранимых значений поля “количество”. В подобном случае процесс материализации называется косвенным.

■ Структура хранимых записей Две существующие хранимые записи можно объединить в одну. Например, хранимые записи imaASDFDSADFSASFSFSFDSDFge.png

■ Структура хранимых файлов

■ что база данных может расти и развиваться, не оказывая влияния на приложения.

Реляционная СУБД

Итак, кратко и не совсем точно можно определить, что реляционная система — это система, основанная на описанных ниже принципах: * Данные рассматриваются пользователем как таблицы (и никак иначе). * Пользователю предоставляются операторы (например, для выборки данных), позволяющие генерировать новые таблицы на основании уже существующих.

Например, в системе обязательно должны присутствовать оператор сокращения, предназначенный для получения подмножества строк заданной таблицы, и оператор проекции, позволяющий получить подмножество ее столбцов. Однако подмножество строк и подмножество столбцов некоторой таблицы, безусловно, можно рассматривать как новые таблицы.

Причина, по которой такие системы называют реляционными, состоит в том, что английский термин “relation” {отношение), по сути, представляет собой общепринятое математическое название для таблиц. Поэтому на практике термины отношение и таблица в большинстве случаев можно считать синонимами, по крайней мере, для неформальных целей.

Прежде всего, старые (дореляционные): системы с инвертированными списками (inverted list), иерархические (hierarchic) и сетевые (network).

im12342534432212435423KJHGFDage.png

Резюме

Итак, подведем итог обсуждению основных вопросов. Систему баз данных можно рассматривать как компьютеризированную систему хранения записей.

Такая система включает сами по себе данные (хранимые в базе данных), аппаратное обеспечение, программное обеспечение (в частности, систему управления базами данных, или СУБД), а также пользователей (что наиболее важно).

Пользователи, в свою очередь, подразделяются на прикладных программистов, конечных пользователей и администраторов базы данных, или АБД.

Последние отвечают за администрирование базы данных и всей системы баз данных в соответствии с требованиями, устанавливаемыми администратором данных.

Базы данных являются интегрированными и чаще всего совместно используемыми. Они применяются для хранения перманентных данных.

Можно считать (хотя это и не совсем точно), что эти данные представляют собой сущности и связи между этими сущностями, хотя сами связи — это, по сути, просто специальный вид сущности. Очень кратко мы рассмотрели понятие диаграмм “сущность—связь”.

Система баз данных имеет ряд преимуществ, наиболее важным из которых является физическая независимость от данных.

Независимость от данных может быть определена как невосприимчивость прикладных программ к изменениям способа физического хранения данных и используемых методов доступа.

Среди всего прочего для обеспечения независимости от данных требуется строгое разделение между моделью данных и ее реализацией. (По ходу изложения напоминаем, что термин модель данных, к нашему сожалению, имеет два различных значения.)

Системы баз данных обычно поддерживают транзакции, или логические единицы работы.

Основное преимущество транзакций заключается в том, что они гарантируют неразрывность выполняемых действий (по принципу “все или ничего”), несмотря на возможные сбои системы, имевшие место до завершения выполнения транзакции.

Наконец, система баз данных может быть основана на нескольких различных подходах. Реляционные системы базируются на формальной теории, называемой реляционной моделью, в соответствии с которой данные представляются в виде строк в таблицах (и интерпретируются как истинные высказывания), а пользователям предоставляется возможность использовать операции, обеспечивающие поддержку процесса логического вывода дополнительных истинных высказываний как следствий из существующих данных.

И с экономической, и с формальной точек зрения можно считать, что реляционные системы являются наиболее важным сегментом рынка баз данных (и это положение дел, по-видимому, не изменится в обозримом будущем). Мы рассмотрели несколько примеров использования языка SQL — стандартного языка для работы с реляционными системами (в частности, были приведены примеры операторов SELECT, INSERT, DELETE И UPDATE этого языка). Материал данной книги в значительной мере ориентирован на реляционные системы и в меньшей мере — на сам язык SQL (по причинам, указанным в предисловии).

Реляционная алгебра

Реляционная алгебра базируется на теории множеств и является основой логики работы баз данных.

Реляционной базой данных называется совокупность отношений, содержащих всю информацию, которая должна хранится в базе. В данном определении нам интересен термин отношение, но пока оставим его без строго определения.

Таблица \(PRODUCTS\)

ID

NAME

COMPANY

PRICE

123

Печеньки

ООО ”Темная сторона”

190

156

Чай

ООО ”Темная сторона”

60

235

Ананасы

ОАО ”Фрукты”

100

623

Томаты

ООО ”Овощи”

130

У таблицы 4 строки. Строка - это кортеж реляционной теории.

Отношение - это множество упорядоченных кортежей.

Домены применительно к таблице - это столбцы.

Пусть даны N множеств D1,D2, …. Dn (домены), отношением R над этими множествами называется множество упорядоченных N-кортежей вида <d1,d1,…dn>, где d1 принадлежит D1 и тд.

Множества D1,D2,..Dn называются доменами отношения R.

Каждый элемент кортежа представляет собой значение одного из атрибутов, соответствующего одному из доменов.

Ключи в отношениях

В отношении требованием является то, что все кортежи должны различаться. Для однозначной идентификации кортежа существует первичный ключ. Первичный ключ это атрибут или набор из минимального числа атрибутов, который однозначно идентифицирует конкретный кортеж и не содержит дополнительных атрибутов.

Подразумевается, что все атрибуты в первичном ключе должны быть необходимыми и достаточными для идентификации конкретного кортежа, и исключение любого из атрибутов в ключе сделает его недостаточным для идентификации.

Для таблицы PRODUCTS первичным ключом будет являться сочетание атрибутов из первого и второго стобца

Для таблицы \(DRIVERS\) первичный ключ СОСТАВНОЙ

COMPANY

DRIVER

ООО ”Темная сторона”

Владимир

ООО ”Темная сторона”

Михаил

ОАО ”Фрукты”

Руслан

ООО ”Овощи”

Владимир

В реляционной БД таблицы взаимосвязаны и соотносятся друг с другом как главные и подчиненные. Связь главной и подчиненнной таблицы осуществляется через первичный ключ (primary key) главной таблицы и внешний ключ ( foreign key ) подчиненной таблицы. Внешний ключ это атрибут или набор атрибутов, который в главной таблице является первичным ключем.

Операции реляционной алгебры:

  • Основные восемь операций реляционной алгебры были предложены Э.Коддом.

  • Объединение

  • Пересечение

  • Вычитание

  • Декартово произведение

  • Выборка

  • Проекция

  • Соединение

  • Деление

Для понимания важно запомнить, что результатом любой операции алгебры над отношениями является еще одно отношение, которое можно потом так же использовать в других операциях.

Создадим еще одну таблицу \(SELLERS\), которая нам пригодится в примерах.

ID

SELLER

123

OOO “Дарт”

156

ОАО ”Ведро”

235

ЗАО “Овоще База”

623

ОАО ”Фирма”

Условимся, что в этой таблице ID это внешний ключ, связанный с первичным ключом таблицы PRODUCTS.

ПРОЕКЦИЯ

Проекция является операцией, при которой из отношения выделяются атрибуты только из указанных доменов, то есть из таблицы выбираются только нужные столбцы, при этом, если получится несколько одинаковых кортежей, то в результирующем отношении остается только по одному экземпляру подобного кортежа.

Для примера сделаем проекцию на таблице PRODUCTS выбрав из нее ID и PRICE.

\(\pi_{ID,PRICE} PRODUCTS\)

В результате этой операции получим отношение:

ID

PRICE

123

190

156

60

235

100

623

130

Выборка

Выборка — это операция, которая выделяет множество строк в таблице, удовлетворяющих заданным условиям. Условием может быть любое логическое выражение. Для примера сделаем выборку из таблицы с ценой больше 90.

\(\sigma_{PRICE>90} PRODUCT\)

ID

NAME

COMPANY

PRICE

123

Печеньки

ООО ”Темная сторона”

190

235

Ананасы

ОАО ”Фрукты”

100

623

Томаты

ООО ”Овощи”

130

\(\sigma_{(PRICE>90)\cap (ID<300)} PRODUCT\)

ID

NAME

COMPANY

PRICE

123

Печеньки

ООО ”Темная сторона”

190

235

Ананасы

ОАО ”Фрукты”

100

Совместим операторы проекции и выборки. Мы можем это сделать, потому что любой из операторов в результате возвращает отношение и в качестве аргументов использует также отношение.

Из таблицы с продуктами выберем все компании, продающие продуты дешевле 110.

\(\pi_{COMPANY} \sigma_{PRICE<100} PRODUCTS\)

COMPANY

ООО ”Темная сторона”

ОАО ”Фрукты”

Умножение

Умножение или декартово произведение является операцией, производимой над двумя отношениями, в результате которой мы получаем отношение со всеми доменами из двух начальных отношений. Кортежи в этих доменах будут представлять из себя все возможные сочетания кортежей из начальных отношений.

Получим декартово произведения таблиц \(PRODUCTS\) и \(SELLERS\). Синтаксис операции:

\(PRODUCTS\)

ID

NAME

COMPANY

PRICE

123

Печеньки

ООО ”Темная сторона”

190

156

Чай

ООО ”Темная сторона”

60

235

Ананасы

ОАО ”Фрукты”

100

623

Томаты

ООО ”Овощи”

130

\(SELLERS\)

ID

SELLER

123

OOO “Дарт”

156

ОАО ”Ведро”

235

ЗАО “Овоще База”

623

ОАО ”Фирма”

\(PRODUCTS\) × \(SELLERS\)

Можно заметить, что у двух этих таблиц есть одинаковый домен ID. В подобной ситуации домены с одинаковыми названиями получают префикс в виде названия соответствующего отношения, как показано ниже.

message/text Example: Blue is my favorite color.

\((\sigma_{ID<235} PRODUCT)\) ×\((\sigma_{ID<235} SELLERS)\)

ID

NAME

COMPANY

PRICE

SELLERS.ID

SELLER

123

Печеньки

ООО ”Темная сторона”

190

123

OOO “Дарт”

156

Чай

ООО ”Темная сторона”

60

156

ОАО ”Ведро”

123

Печеньки

ООО ”Темная сторона”

190

156

ОАО ”Ведро”

156

Чай

ООО ”Темная сторона”

60

123

OOO “Дарт”

Для примера использования этой операции представим себе необходимость выбрать продавцов с ценами меньше 90. Без произведения необходимо было бы сначала получить ID продуктов из первой таблицы, потом по этим ID из второй таблицы получить нужные имена SELLER, а с использованием произведения будет такой запроc:

\(\pi_{SELLER}\sigma_{PRODUCT.ID=SELLERS.ID \cap PRICE<90} PRODUCT x SELLERS\)

SELLER

ОАО “Ведро”

НЕ ЗАКОНЧИЛ

Лекция 1 (2) ПАД

iASDFDSAFASFSFDFmage.png

Предпосылки появления баз данных: * необходимость хранить и обрабатывать большой объем данных * совместное использование информации * внешние носители

  • Информация – сведения о каком-либо событии, объекте или процессе.

  • Данные – информация, представленная в определенном виде, позволяющем автоматизировать ее сбор, хранение и дальнейшую обработку.

  • Управление данными – совокупность функций для требуемого представления данных, их накопления и хранения, обновления, удаления, поиска по заданному критерию и выдачи данных.

  • База данных (БД) – поименованная совокупность взаимосвязанных структурированных данных, которая отражает состояние объектов и их отношений в рассматриваемой предметной области, причем они организованы так, чтобы обеспечить независимость данных от программ обработки.

  • Предметная область – информация о части реального мира, подлежащая изучению с целью организации управления и автоматизации.

  • Система управления базами данных (СУБД) – совокупность программ и языковых средств, предназначенных для управления данными в базе данных, ведения базы данных и обеспечения взаимодействия её с прикладными программами

  • Транзакция – это единица действий, производимых с базой данных. В состав транзакции может входить несколько операторов изменения базы данных, но либо выполняются все эти операторы, либо не выполняется ни один. СУБД, кроме ведения собственно базы данных, ведет также журнал транзакций.

imaSFDFSDFDSFDSFSDGFDGFGDFGge.png

Проектирование БД - процесс последовательного отображения исследуемых явлений реального мира в данных в памяти ЭВМ

iQWEW232REDF3W4ERTmage.png

Концептуальная модель - это отражение предметной области, для которой разрабатывается база данных

imaADSFDBGNHJKLJHGFDge.png

imagDSFG132213FHGFDSASFGDSe.png

  • Концептуальная модель представляется в виде диаграммы сущностей – связей (entity – relationship) или ER-диаграммы.

  • Процесс построения ER-диаграммы называется ER-моделированием.

  • Сущность (Entity или объект) – о чем накапливается информация в информационной системе (нечто такое, за чем пользователь хотел бы наблюдать).

  • Атрибут – поименованное свойство (характеристика) сущности.

imaDSFSFSFFDSE33EE3ge.png

  • Связь 1:1. Одиночный экземпляр сущности одного класса связан с одиночным экземпляром сущности другого класса…

  • Связь 1:M. Единый экземпляр сущности одного класса связан со многими экземплярами сущности другого класса

  • Связь M:N. Несколько экземпляров сущности одного класса связаны с несколькими экземплярами сущности другого класса.

imDSFSDFDSFDSFDFGDDFage.png

Модель должна: * адекватно отражать представление пользователя о данных; * давать возможность ответа на возможные запросы пользователя, причем с минимальными затратами по количеству просматриваемых сущностей; * представлять данные с минимальным дублированием.

iDSFDFDSFDSFDSFDSFDSFSFDSmage.png

Лекция 1 (?) ПАД

Начало лекции

\(R_i\) - отношение

  • Каждое отношение - это по сути своей табличка, исходя из определения

\(A_j\) - атрибут, т.е. столбцы

Отношения ниже не связаны, т.к. у них нет одинаковых атрибутов:

\(R_1 (A_1, A_2, A_3, A_4)\)

\(R_2 (A_5, A_6)\)

Отношения связаны:

\(R_1 (A_1, A_2, A_3, A_4)\)

\(R_4 (A_1, A_5)\)

\(R_3 (A_7, A_8)\)

\(R_4 (A_1, A_5)\)

\(R_5 (A_1, A_7)\)

\(R_6 (A_5, A_7)\)

ДЗ: НАДО ПРИДУМАТЬ ПРЕДМЕТНУЮ ОБЛАСТЬ, В КОТОРОЙ ВЫ ДЛЯ КАЖДОГО ИЗ ЭТИХ ОТНОШЕНИЙ ПРИДУМЫВАЕТЕ РЕАЛЬНЫХ ОБЪЕКТ И ЕГО ХАРАКТЕРИСТИКИ. Хотя бы отношения должны попасть в эту предметную область

Пример:

R1 - студент * А1 - Код унивесритеа * А2 - фио * А3 - номер группы * А4 - номер паспорта

R4 -университет * А1 - КОД УНИВЕРСИТТА * А5 - НАЗВАНИЕ УНИВЕРСИТА

imaDSFSDFDFFDGGHGHGJJGGHJge.png

Реляционная модель данных

  • Отношение - таблица, столбцы которой соответсвуют атрибутам сущности. Каждый атрибут может принимать определенное множество значений (домен)

  • Кортеж - строка таблицы с конкретными значениями полей (экземпляр записи). Поля таблицы элементарные (неделимые)

  • Первичный ключ - минимальный набор атрибутов, однозначно идентифицирующий кортеж в отношении.

Отношение:

—

Первичный ключ

Атрибут

Кортеж

154

х

Групповое отношение:

  1. В таблицы, соответсвующие группам - членам отношения, добавляется столбцы ключевых полей (атрибутов( другого члена отношения (связь описывается через ключевые атрибуты)

  2. групповое отношение определяется как дополнительная группа (дополнительная таблица). Столбцами этой дополнительной таблицы являются ключи групп - членов отношения.

Для формального описания таблицы используется теоретико-множественное понятие отношения.

Схемой отношения \(R\) называется перечень имен атрибутов отношения (соответсвующих столбцам таблицы) с указанием доменов этих атрибутов и обозначается \(R(A_1, A_2, ..., A_n) : \{A_i\}\subseteq D_i\), где \(\{A_i\}\) - множество значений, принимаесях атрибутов \(A_i\) \((i=1,...,n)\).

Совокупность схем отношений, используемых для представления концептуальной модели, называется схемой реляционной базы данных, а текущеие значений соответсвующих отношений - реляционной базой данных.

Пусть \(A_1, A_2, ..., A_n\) - имена атрибутов. Каждому имени \(A_i\) соответсвует допустимое множество хначений, которое может принимать атрибут \(A_i\). Это множество значений \(D_i\) называется доменом атрибута \(A_i\) \((i=1,...,n)\).

По определению домены являются непустыми конечными или счетными множествами

  • Пусть \(D=D_1\cup D_2\cup ... D_n\). Тогда отношением :math:`r` со схемой :math:`R` называется конечное множество отображений \(\{t_1,t_2,...,t_p\}\) из множества \(R:\{A_1,A_2,...,A_n\}\) в множество \(D:\{D_1\cup D_2 \cup ... D_n\}\), таких что \(t_k(A_i)\in D_i\), \(k=1,...p\), \(i=1,...n\)

  • Отображение :math:`t_k` называется \(k\)-м кортежем, \(n\) - размерность кортежа.

  • Понятию :math:`k`-го кортежа соответсвует множество значений, стоящих в \(k\)-й строке рассматриваемой таблицы.

  • Понятию отношения :math:`r` соответствует множество значений, стоящих во всех строках рассматриваемой таблицы.

  • Ключом отношений r со схемой R называется минимальное подмножество $K={A_{i1}, A_{i2},…A_{im}} \subseteq `{A\_{1}, A\_{2},...A\_{n}} $, где :math:{i_1, i_2,…i_m}subseteq {1, 2,…n}` такое, что любые два кортежа \(t_1,t_2 \in r(t_1 \neq t_2)\) не совпадают со значениями множества \(K=\{A_{i1}, A_{i2},...A_{im}\}\).

Свойства отношений

  1. Имеет имя, отличающееся от имен всех других отношений

  2. Каждое значение элементов кортежей представляется простым (атомарным) типом данных

  3. Каждый атрибут имеет уникальное имя

  4. Значения всех атрибутов являются атомарными (неделимыми): следует из определения домена как множества значений простого типа данных, т.е. среди значений домена не могут содержаться множества

  5. Порядок рассмотрения атрибутов в схеме отношения (отношении) не имеет значения: т.к. для ссылки на значение атрибута в кортеже отношения всегда используется имя атрибута

  6. Порядок рассмотрения кортежей в отношении не имеет значения: т.к. отношение представляет собой множество кортежей, а элементы множества, по определению теории множеств, неупорядочены

Работа с данными в реляционной модели

При работе с данными в реляционной моделии используется два формальных аппарата: * реляционная алгебра, основанная на теории множеств * реляционное исчисление, основанное на исчислении предикатов первого порядка

Предикат первого порядка - дизъюнкция, конъюнкция, XOR, отрицание, дополнение

Операции в реляционной алгебре

  • Запрос, представленный на языке реляционной алгебры, может быть реализован как последовательность элементарных алгебраических операций с учетом их старшинства и возможного наличия скобок.

  • Любой запрос к БД можно представить в виде последовательности, составленной из пяти основных операций релицонной алгебры.

1. Объединение :math:`rcup s`

Объединением отношений \(r\) и \(s\) называется множество кортежей, которые принадлежат или \(r\), или \(s\), или им обоим. Для операции объединения требуется одинаковая арность отношений

imaE32E3DESFDSFSDFSFSDFSge.png

Пояснение: новую строку добавили, а повторяющуюся убрали. Дубликатов быть не должно. Мы получили 4 уникальных записи. С точки зрения формальной алгебры - это правильно, но в SQL есть 2 способа: удаление дубликатов и без их удаления

В картинке не хватает колонок с названиями. Мы не можем говорить, что мы работаем с реляционной базой данных, если у нас нет названий атрибутов

2. Разность :math:`r-s`

Разностью отношений r и s называется множество кортежей принадлежащих r, но принадлежащих s. Для этой операции требуется одинаковая арность отношений.

imSDFDSFDGFDGFHGFFHFage.png

  • С помощью операции разности может быть реализовано удаление кортежа из изменющегося отношения. В этом случае \(r\) - исходное отношение, \(s\) - отношение, содержащее один удаляемый кортеж

  • Строки (b g a) нет в пересечении

3. Пересечение :math:`rcap s`

Пересечением отношений r и s называется множество кортежей принадлежащих как r, так и s. Пересечение может быть выражено черз операцию разности

\(r \cap s=r-(r-s)\)

imDSFDSFDSFSAWEWQQWQWQWEage.png

4. Соединение

Соединением отношений \(r\) и \(s\) по стобцам \(A_i\) и \(A_j\) представляет собой множество кортежей в декартовом произведении \(r\) и \(s\), что \(i\)-ый компонент r находится в отношении с \(j\)-ым компонентом \(s\), где - арифметический оператор сравнения. Если является оператором равенства, то эта операция называется эквисоединением.

\(r\underset{\rm i \theta j}{\rm \triangleright \triangleleft} s=\sigma_{i\theta(i+j)} (r \times s)\)

imaDFDSFDDGER5TDTTR43E4DT5ge.png

Примечание: сначала декартово произведение. Получаем матрицу 6 на 5. Т.о. 3 строки мы отбросили.

(2)<(1) - условие селекции: значение второго атрибута первой таблицы меньше, чем значения первого атрибута второй таблицы.

i12312334ERWSFDFFRDSmage.png

Cлужащий:

Фамилия

Код отдела

Иванов

34

Петров

36

Сидоров

34

Сергеев

34

Отдел:

Название

Код отдела

Бухгалтерия

34

Маркетинг

36

Результат соединения:

Служащий.Фамилия

Служащий.Код отдела

Отдел.Название

Отдел.Код отдела

Иванов

34

Бухгалтерия

34

Петров

36

Маркетинг

36

Сидоров

34

Бухгалтерия

34

Сергеев

34

Бухгалтерия

34

5. Декартово произведение :math:`r times s`

Пусть \(r\) и \(s\) - отношениея арности \(k_1\) и \(k_2\) соответсвенно. Декартовым произведением \(r \times s\) называется множество кортежей длины \(k_1+k_2\), первые \(k_1\) компонентов которых образуют кортежи, принадлежащие \(r\), а последние \(k_2\) -кортежи, принадлежащие \(s\).

Декартово произведение:

imaFSDFSDFFGFGHGHGHJHGGHJge.png

imaDSFSDFSFGFDGHGFHGHFGHFGge.png

Примечание: Берем первую строчку aba и берем все строки из \(s\) и получаем строки ababga и abadaf. К остальным строкам аналогично.

Получаем в итоге все возможные соответсвия двух таблиц.

Число столбцов = Число строк = число строк \(r\) \(\times\) число строк \(s\)

**5. Селекция $ \sigma_{F}(r)$**

Пусть \(F\) - формула, образованная: операндами, являющимимся константами или именами атрибутов, арифметическими операторами сравнения, логическими операторами (и, или, не), тогда селекцией называется множество кортежей, компоненты которого уовлетворяют условию, заданному формулой \(F\)

imDSFDFSDFW3EE222E2age.png

ПРИМЕЧАНИЕ: по некоторому условию отбрасывает строки. Оставляем только те, строки в которых первый и третий элемент равны

~фильтрация

**6. Проекция $ \pi_{A_i1, A_i2,…,A_im}(r)$**

Проекция - множество кортежей, получаемых из кортежей отношения \(r\) выбором столбцов с именами \(A_i1, A_i2,...,A_im\).

im21WQDSSDWEWDSFEDage.png

Домашнее задание

Надо придумать больше, чем 3 атрибута. При этом придумать таким образом, чтобы запросы 1 и 2 имели смысл

ПРИМЕРЫ: 1. $ \sigma{(A_3)+35<date}(r1)$ = оставить только те строчки, у которых после прибавления третьему атрибуту числа 35 меньше константы. 2. $ :nbsphinx-math:`pi`{A_1, A_3}(\sigma{(A_3)+35<date}(r1))$ = Оставляем первый и третий стобцей, где первый столбец :math:`date` 3. $ rp2 :nbsphinx-math:`pi`{A_1}(rp1)$ =

SQL

SQL (Structured Query Language) - декларированный язык программирования, применяемый для создания, модификации и управления данными в реляционной БД, управляемой соответсвующей СУБД.

Программная оболочка, реализующая реляционную модель данных

Преимущества и недостатки:

+ Независимость от конкретной СУБД

+ Наличие стандартов

+ Декларированность

- Сложность

- Несоответствие реляционной модели данных: 1. Разрешены строки-дубликаты 2. Разрешены столбцы без имени и дублирующие имена столбцов

Операторы

  • DDL - Data Definition Language - для создания базы данных (таблиц, индексов и т.д.) и редактирования ее схемы

  • DML - Data Manipulation Lanuage - язык обработки данных. Содержит операторы для внесения изменений в содержимое таблиц базы данных

  • DCL - Data Control Language - язык управления данными. Содержит операторы для разграничения доступа пользователей к объектам базы данных.

  • TCL - Transaction Contol Language

  • DDL - Data Definition Language

  1. CREATE

  2. ALTER

  3. DROP

  1. CREATE

Создаем таблицу в базе данных

[2]:

%%sql
CREATE TABLE item_loc(
Location int not null,
Item int not null,
Item_Name varchar(50) not null,
Status char(1) not null,
Status_date_update date not_null,
Stock_on_hand real);

  Cell In [2], line 2
    CREATE TABLE item_loc(
           ^
SyntaxError: invalid syntax

imagDSFSDFFGDFHGFDGHGFDSe.png

Синим выделены системные слова.

CREATE - оператор создать

TABLE - что мы создаем

item_loc - произвольное название таблицы, как хотим называем. Название отношения.

(В скобках описываем структуру нашей таблицы. )

Location - первый атрибут. тип данных int. not null - не может быть строка с пустым данным атрибутом

…

Item_Name. varchar(50) - изменяемый по размерности хранения символов

Status. char(1) - один символ размерности 1. not null

Status_date_update. date - дата время. not_null,

Stock_on_hand. real - число с плавающей точкой)

  1. ALTER

Позволяет изменять структуру в базе данных чего-то

imSADSADADSADASDDFGDGFDage.png

Мы пишем ALTER.

Затем тип изменяемого объекта = TABLE

Затем название изменяемого объекта.

  1. DROP

im12E3WDFSEW34REFT56GFY67TYage.png

Лекция 2 (?) ПАД

Проверка ДЗ

iDSFSDFSFGFGHFDSADFFDSFEWWRTYTmage.png

imaSDFSDFSDFSDFSDFSDDSFDFSge.png

imagDSFDSFDSFDGFHJKYJGTFHGRFe.png

i3E2WRR3EWFWE3WFRFWEFDFmage.png

im132E3RETGRTE32WREGFRER3Eage.png

iSDFDSFDFGDGHJGFDSASFDGFHage.png

DML

DML - это все то, что позволяет изменять и манипулировать

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

  • MERGE

  • TRUNCATE

БАЗОВЫЙ СИНТАКСИС ЗАПРОСА SELECT:

Инструкция select состоит из нескольких компонентов, или предложений. Хотя только одно из них является обязательным при использовании MySQL (а именно, предложение select), обычно в запрос включается по крайней мере два или три из шести доступных предложений.

im123EWF23EWR23WEFage.png

[ ]:

SELECT [DISTINCT] # Порядок слов важен
FROM table # из таблицы
[WHERE] # УСЛОВИЕ ФИЛЬТРАЦИИ:
[GROUP BY] # АГРЕГАЦИЯ ПО КАКОМУ-ТО ИЗ ВИДА ПОЛЕЙ
[HAVING]
[ORDER BY]
[UNION] <SELECT ...>

[ ]:

SELECT location, count(item) as qty # вытащи поле Location из таблицы item_loc
FROM item_loc
WHERE location>1000 and status='A'
GROUP BY location
HAVING count(item)>4000
order by LOCATION

Cеминар (который очно был)

lead_time - с момента заказа

cycle_time - начало производства

\(LT \geq CT\)

iSDFSDFDSFGHGFDSFHDFDREWTRY54mage.png

Разница рангов можно сравнивать в случае несравнимых показателей.

Например. как сравнивать скорость производства и количество людей. По рангам. Вторичная метрика - сравнение рангов.

Гипотеза подтверждена.

Лекция 3 (?) ПАД

Прикладной кейс по базам данных

У НАС БУДЕТ СЕМЕСТРОВОЕ ЗАДАНИЕ

Пример проекта: продажа сока “СЕМЬЯ”

Нужно сделать небольшое исследование, по 5 человек норм - группы.

[1]:

%%sql

UsageError: Cell magic `%%sql` not found.

[2]:

%%sql
DELETE FROM table
[WHERE]

TRUNCATE TABLE table

UsageError: Cell magic `%%sql` not found.

JOIN - оператор соединения таблиц по некоторому условию

[ ]:

SELECT * FROM TABLE_A AS A
INNER JOIN TABKE_B AS B
ON a.variable_a=b, variable_b
LEFT JOIN TABLE_C AS C
ON a.variable_a=c.variable_c
...

ima12313E2RWFER32WER32ER23WEFF2QWEge.png

Student

id

last_name

first_name

group

facultee

student

id

last_name

first_name

group

course

1

Иванов

Иван

105

3

2

Сидоров

Сидор

209

4

subject

id

name

sem

type

odd

group

facultee

id

descr

pair

  1. 9:00

  1. 10:50

JOIN делает декартово произведение и фильтрацию.

id

F

1

ФЕН

2

ММТ

3

ФФ

4

ЭФ

id

last_name

first_name

group

course

1

Иванов

Иван

105

3

2

Сидоров

Сидор

209

4

id

last_name

first_name

group

course

id

F

1

Иванов

Иван

105

3

1

ФЕН

2

Сидоров

Сидор

209

4

2

ММТ

1

Иванов

Иван

105

3

3

ФФ

2

Сидоров

Сидор

209

4

4

ЭФ

1

Иванов

Иван

105

3

4

ЭФ

2

Сидоров

Сидор

209

4

3

ФФ

1

Иванов

Иван

105

3

2

ММТ

2

Сидоров

Сидор

209

4

1

ФЕН

im23WEFR23WER2EWFFage.png

Леывй JOIN - все записи из таблицчки слева и только те, котоые выполнили условия справа.

i3ED23EWR32EWQW2QW2WDEWFVDDGHGJFDGFmage.png

Вложенный запрос

S

[ ]:

SELECT * FROM TABLE_A AS A
WHERE A_FIELD_1 IN
(SELECT B_FIELD AS B
 ...
)
Домашнняя работа
1.Найти отдел с самой большой зп 2. найти самый большой отдел 3. найдите подчиненного у которого нет руководителя

Семинар (не знаю какой, но второй по SQL)

ima3EWEW2EW2EW324T56576ge.png

Работа на семинаре и ДЗ:

  1. Создать таблицу с выручкой по трекам

  2. Какой покупатель сделал больше всего покупок/на большую сумму

  3. Из какой страны пришло больше выручки

  4. В какой месяц было продано больше всего треков

  5. Какой менеджер продаж работал эффективнее всего

  6. Обновить все цены на 1,99

chinook sample database

im12324W3RET21345T4ET3ERETage.png

  • employees table stores employees data such as employee id, last name, first name, etc. It also has a field named ReportsTo to specify who reports to whom.

  • customers table stores customers data.

  • invoices & invoice_items tables: these two tables store invoice data. The invoices table stores invoice header data and the invoice_items table stores the invoice line items data.

  • artists table stores artists data. It is a simple table that contains only the artist id and name.

  • albums table stores data about a list of tracks. Each album belongs to one artist. However, one artist may have multiple albums.

  • media_types table stores media types such as MPEG audio and AAC audio files.

  • genres table stores music types such as rock, jazz, metal, etc.

  • tracks table stores the data of songs. Each track belongs to one album.

  • playlists & playlist_track tables: playlists table store data about playlists. Each playlist contains a list of tracks. Each track may belong to multiple playlists. The relationship between the playlists table and tracks table is many-to-many. The playlist_track table is used to reflect this relationship

im324RETR234RE2EWER123WERage.png

Только 1 менеджер по продажам!!!!!

Invoice - счет-фактура

Профильное поле счета-фактуры - содержащиеся в счете-фактуре данные, характеризующие одну или несколько позиций, которых касается счет-фактура.

Иногда количества для выручки могут показать аномалии в данных.

10 ТРЕКОВ ЗА 1 ПРИСЕСТ.

Invoice - это шапка.

Invoiceline - деталиация

[ ]:

%%sql
/*1. Создать таблицу с выручкой по трекам
2. Какой покупатель сделал больше всего покупок/на большую сумму
3. Из какой страны пришло больше выручки
4. В какой месяц было продано больше всего треков
5. Какой менеджер продаж работал эффективнее всего
6. Обновить все цены на 1,99 */

[ ]:

%%sql
/*1. Создать таблицу с выручкой по трекам*/
SELECT name, sum(il.Quantity*il.UnitPrice) as revenue
FROM InvoiceLine il
left join Track
On il.TrackId =Track.TrackId
Group by il.TrackId
ORDER by revenue DESC;

[ ]:

%%sql
/*2. Какой покупатель сделал
больше всего покупок/на большую сумму*/
--мое решение
SELECT C.FirstName,
C.LastName,
C.CustomerId,
SUM(il.Quantity*il.UnitPrice) as tot,
count(i.InvoiceId) as qty,
sum(i.Total)
FROM Customer C
LEFT JOIN Invoice i
ON C.CustomerId=i.CustomerId
LEFT JOIN InvoiceLine il
ON i.InvoiceId =il.InvoiceId
GROUP by i.CustomerId
order by qty DESC ;

--правильное решение
SELECT FirstName, LastName,
SUM(Total) as TR, COUNT(i.InvoiceId) as qty
FROM Invoice i
left join Customer c
on i.CustomerId =  c.CustomerId
group by  i.CustomerId
order by qty  DESC;

[ ]:

%%sql
/*3. Из какой страны пришло больше выручки*/
select C.Country,SUM(il.Quantity*il.UnitPrice) as revenue
from Customer C
LEFT JOIN Invoice i
ON C.CustomerId=i.CustomerId
--GROUP by C.LastName;
LEFT JOIN InvoiceLine il
ON i.InvoiceId =il.InvoiceId
GROUP by C.Country
ORDER by revenue DESC;

[ ]:

%%sql
/*4. В какой месяц было продано
 больше всего треков*/
SELECT strftime('%m', i.InvoiceDate) AS month1,
sum(il.Quantity) as tot
FROM Invoice i
LEFT JOIN InvoiceLine il
ON i.InvoiceId =il.InvoiceId
GROUP BY month1
ORDER by tot DESC;

--Проверка
SELECT strftime('%m', i.InvoiceDate) AS month1, sum(il.Quantity) as tot
FROM Invoice i
LEFT JOIN InvoiceLine il
ON i.InvoiceId =il.InvoiceId
where month1 = '10'

[ ]:

%%sql
/*5. Какой менеджер продаж работал
  эффективнее всего*/
/*Почему-то в БД только 1 менеджер по продажам?*/
--C.SupportReplId - номер менеджера

--МОЕ РЕШЕНИЕ
select e.EmployeeId, sum(i.Total) as tot, e.FirstName ,e.LastName
from Employee3 e
left join Customer c
on c.SupportRepId=e.EmployeeId
left join Invoice i
on i.CustomerId=c.CustomerId
group by e.EmployeeId
ORDER by tot DESC;

--РЕШЕНИЕ САШИ ТКАЧЕНКО
SELECT sum(i.Total) as tot, e.LastName ,e.FirstName
FROM Invoice i
left join Customer c on i.CustomerId =c.CustomerId
LEFT JOIN Employee3 e
ON  c.SupportRepId = e.EmployeeId
group by c.SupportRepId
order by tot;

[ ]:

%%sql
/*6. Обновить все цены на 1,99 */
UPDATE Track
set UnitPrice=1.99
where UnitPrice>5

Домашнее задание

Update, Like, Case, Insert

  1. Вывести все треки, которые

    а. Начинаются с Wild, б. Заканчиваются на Wild, в. Содержат Wild.

  2. Обновить e-mail работников по типу lastname.firstname@chinookcorp.com (а. Только у CEO, б. у первых трёх работников, в. у всех)

  3. Выбрать всех покупателей, добавив колонку со значениями foreign - для стран не USA и domestic для USA

  4. Добавить три разных артиста в таблицу Artist

  5. Добавить в таблицу employee столбец с зарплатой (значения любые)

  6. Добавить тригер логгирующий изменения в столбце зарплат

Комментарии по ДЗ

  1. * - это все что дальше (слева). В каких-то SQL %

  2. UPDATE И НАДО ИСПОЛЬЗОВАТЬ КОНКАТЕНАЦИЮ СТРОК

  3. add column

  4. insert трех строчек

  5. alter table update

  6. это мы будем делать на семинаре

Лекция (Не знаю какая) (одну пропустил)

ДЗ С ПРОШЛОЙ ЛЕКЦИИ

Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек

ima12331423R2314ERWEFGR3ERWge.png

Мы считаем число строк employee, оставляя только удовлетворяющие условию

[11]:

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

[21]:

%%sql
select department_id from employee
group by department_id having count(*)<=3

Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

[27]:

%%sql
select department_id from employee
group by department_id having count(*)<=3

Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

[34]:

%%sql S
select department_id from employee
group by department_id having count(*)<=3

Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

Задача 4

Вывести список сотрудников, не имеющих назначеннного руководителя, работающего в том же отделе.

Домашнаяя работа

ЗАДАЧА 5

Аналитические функции SQL

Чаще всего с ним работают аналитики. Они одинаковые для

Позволяют лакнично аписывать запросы без потери производительстности

Цель владения

  1. Лучшая лакончиность обработки запросов …

  2. Лаконичная и простая формулировака

Как пишуся аналитические функции select column_1, column_2

аналитическая функция(column_N),[…] ORDER BY column_2,[…] rows between current row and unbounded following

[ ]:

%%sql
аналитическая функция(column_N),[...] ORDER BY column_2,[...]
rows between current row and unbounded following
)

Доп. задания на Quiries БД chinook

Task # 1: Prerequisites

  1. Download Chinook Database script for yours DBMS. Use this link for Chinook repository

  2. Install it

  3. Build an ERD

  4. Write report with info about tables (names and types of columns and number of rows in each)

Task # 2: Query Practice (part 1)

Use the Chinook Database and the DB Browser for your Database Server For each of the following exercises, provide the appropriate query (or some queries). Obviously provide results for each query! Keep your successful queries in a task2.sql file with comments. 1. Provide a query showing Customers (just their full names, customer ID and country) who are not in the US. 2. Provide a query only showing the Customers from Brazil. 3. Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer’s full name, Invoice ID, Date of the invoice and billing country. 4. Provide a query showing only the Employees who are Sales Agents. 5. Provide a query showing a unique list of billing countries from the Invoice table. 6. Provide a query that shows the invoices associated with each sales agent. The resultant table should include the Sales Agent’s full name. 7. Provide a query that shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers. 8. How many Invoices were there in 2009 and 2011? What are the respective total sales for each of those years? 9. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for Invoice ID 37. 10. Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice. HINT: GROUP BY

Task # 3: Query Practice (part 2)

Use the Chinook Database and the DB Browser for your Database Server For each of the following exercises, provide the appropriate query (or some queries). Obviously provide results for each query! Keep your successful queries in a task3.sql file with comments. 1. Provide a query that includes the track name with each invoice line item. 2. Provide a query that includes the purchased track name AND artist name with each invoice line item. 3. Provide a query that shows the # of invoices per country. HINT: GROUP BY 4. Provide a query that shows the total number of tracks in each playlist. The Playlist name should be included on the resultant table. 5. Provide a query that shows all the Tracks, but displays no IDs. The resultant table should include the Album name, Media type and Genre. 6. Provide a query that shows all Invoices but includes the # of invoice line items. 7. Provide a query that shows total sales made by each sales agent. 8. Which sales agent made the most in sales in 2009? 9. Which sales agent made the most in sales in 2010? 10. Which sales agent made the most in sales over all?

Task # 4: Query Practice (part 3)

Use the Chinook Database and the DB Browser for your Database Server For each of the following exercises, provide the appropriate query (or some queries). Obviously provide results for each query! Keep your successful queries in a task4.sql file with comments. 1. Provide a query that shows the # of customers assigned to each sales agent. 2. Provide a query that shows the total sales per country. 3. Which country’s customers spent the most? 4. Provide a query that shows the most purchased track of 2013. 5. Provide a query that shows the top 5 most purchased tracks over all. 6. Provide a query that shows the top 3 best selling artists. 7. Provide a query that shows the most purchased Media Type.

[ ]:



[ ]:

Включение хайлайтинга кода SQL в JN

ima12343EWDEWED23ER123243213243ge.png

Для начала мы клонируем репозиторий расширения для JN с Guthub

Устанавливаем расширение из директории клонированного репозитория на жесктом диске

[ ]:

!jupyter nbextension install C:/Users/79618/Desktop/notebook_magiclight-master

Активация расширения

[1]:

!jupyter nbextension enable notebook_magiclight-master/index

Enabling notebook extension notebook_magiclight-master/index...
      - Validating: ok

Загрузка базы данных Sekila из командной строки

  1. Мы скачиваем архив с БД sakila database c сайта MySQL и разархивируем его на жесктий диск

  1. Затем запускаем MySql Command Line Client и вводим пароль

  1. Подключаемся к серверу MySQL, используя следующую команду

[38]:

%%sql
$> mysql -u root -p

Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

  1. Запускам sakila-schema.sql скрипт, чтобы создать структуру БД

!!! Нужно обязательно писать через /, несмотря на то, что путь в Windows задается через \.

!!! Не забываем в конце каждой строки кода ставить ;

[39]:

%%sql
mysql>  SOURCE C:/Users/79618/Desktop/DATABASES/sakila-db/sakila-schema.sql;

Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

  1. Далее запускаем скрипт sakila-data.sql, чтобы заполнить структуру БД.

[ ]:

%%sql
mysql> SOURCE C:/Users/79618/Desktop/DATABASES/sakila-db/sakila-data.sql;

  1. Проверяем корректность установки

[ ]:

mysql> USE sakila;
Database changed

[ ]:

mysql> SHOW FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.01 sec)

[ ]:

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

[ ]:

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

Нетология

Базы данных - это структурная совокупность взаимосвязанных данных определенной предметной области: реальных объектов, процессов, явлений и т.д.

imSDFSDFDSFDGERWEWECEWCECDCCWDWEWEWEage.png

imaREWREWRCR2R232C32Cge.png

iWECWRECEWRCWERCWERCWRCmage.png

imaEWCRWCVCEWCR22C2Cge.png

Реляционная модель построенная с помощью реляционной алгебры. Таким образом мы гарантируем, что так информация будет храниться в надежном виде.

ОО-модель. Мы получаем удобство того, что данные имеют общую типизацию, мы не имеем проблем с интеграцией данных.

NoSQL-системы. Гибкие структуры. Их проектирование происходить “налету”. Скорость работы также значительно выше, чем в классических реляционных СУБД.

Иерархические, когда есть родительские элементы и дочерные элементы. Причем у каждого элемента только один родитель.

Сетевая, очень похожа, но родителей может быть несколько. Например, база данных обраховательной системы.

Графовые СУБД подразумевают, что есть узлы. Распространены в логистике. Инфа по складам, автомобилям, дорогам, соцсетям

Документо-ориентировання СУБД. Закрывает пласт всех возможных задач. Данные хранятся по принципу JSON.

СУБД ключ-значение. Самая быстрая СУБД, т.к. работает в оперативной памяти.

Колоночный СУБД. Перевернутая на 90 градусов СУБД.Часто используется в аналитике. Селект-запросы можно не считывать все данными. Поэтому применяется с большими данными.

im1FDSREWRRFRTGRT5YTH21321age.png

imaFSC4VCTERFC4FSRge.png

imaCSEDV5RFB6TF6YTVECR4VT5RVge.png

imA3WRS4ETDRVTF5VTV5DTage.png

im4WEV5TRY6BRTE54WSEage.png

ima3WRE4TV5VT4R3EQ3WCEge.png

ima32W4VE5RE4F3ge.png

ima43W5E5GF4ET5VRYTRFETge.png

im2Q4FW5E4F4D34Eage.png

Требования BASE и ACID

im4W3VET45D54V6R5B7R554CW4E5age.png

Базовая доступность. В первоую очередь данные всегда должны быть доступны безотносительно их достоверности.

Неусточивое состояние. Когда-нибудь данные в ситеме будут зафисированы в плане.

Конечная согласованность. В конечном счете рано или поздно вся информация будет согласованной, т.е. она будет идентична для всех систем.

imRESDTBFHTDRCSESREDTVFage.png

Задача ACID-системы - это достоверность информации.

Атомарность. Если информация на нескольких серверах, то на их всех она должна быть идентична. Если на первом сервере инфа обновилась, то пользователь получит инфу оттуда только после обновления инфы на всех остальных серверах.

Согласованность. Когда пользователь запрашивает информацию - открывается транзакция. На момент начала и окончания транзакции информация должны быть согласованной.

Изоляция. На момент работы транзакции происходит блокировка данных

Долговечность. Неважно, что произойдет с данными. Всегда есть возможность восстановаить данные в согласованном виде.

imaSEDTRFBED4FW3SFE5Rge.png

Теоремы CAP и PACELC

Теорема CAP (теорема Брюера) - эвристическое утверждение о том, что в любой реализации распределенных вычислений возможно обеспечить не более двух из трех следующих свойств:

  1. Согласованность данных (consistency) - как только мы успешно записали данные в наше распределенное хранилище, любой клиент при запросе получит эти последние данные

  2. Доступность (availability) - в любой момент может получить данные из нашего хранилища или ответ об их отсутствии, если их никто еще не сохранил

  3. Устойчивость к разделению (partition tolerance) - потеря сообщения между компонентами системы или даже потеря всех сообщений не влияет на роботоспособность системы. Важный момент заключается в том, что выход каких-то компонентов из строя тоже не повлияет на роботоспособность системы, так как можно считать, что эти компоненты просто теряют связь со всей остальной системой

imaWESFDGRTFGEWRT45RTERETRREge.png

imagDSFFDFESWWqerrgdrfe.png

В современных СУБД на практике не существует системы неусточивых к разделению. Т.е. нам надо выбирать либо доступность, либо согласованность.

imaw3ret5rewcrvt45cr4cge.png

Изучаем SQL (Глава 1). Введение

Sakila - база данных, которую сипользую в книге для примеров (а также другие базы данных)

Нереляционные СУБД

Иерархическая СУБД данные представлены в виде одной или нескольких древовидных структур.

imaqwdefrt5ewertythgrfge.png

  • Д - Джордж

  • С - Сью

У клиентов Д и С есть собственные деревья, содержащие их счета и транзакции.

В иерархической СУБД ищет дерево конкретного клиента, а затем выполняет обход дерева в поисках нужных счетов и/или транзакций. У каждого узла в дереве может быть либо 0, либо 1 родительский узел. И может быть либо 0, либо более 0 дочерних узлов. Эта конфигурация с однима родителем

Альтернатива - это сетевая СУБД, которая предоставляет наборы записей и наборы ссылок, которые определяют отношения между различными записями.

imaqwert5ryrt5erw3r4t5by6ge.png

Чтобы найти транзакциии валютных операций на счете С, НЕОБХОДИМО ВЫПОЛНИТЬ СЛЕДУЮЩИЕ ДЕЙСТВИЯ: 1. Найти запись о клиенте С 2. Перейти по ссылке из записи клиента С к списку ее счетов 3. Пройти по цепочке счетов, пока не будет найден валютный счет 4. Перейти по ссылке из записи валютного счета к списку транзакций

imrc4vet5rby6rt5erwe3e4vtrb6age.png

К услугам можно получить доступ из нескольких мест, что позволет сетевой СУБД действовать в качестве иерархии с многими родителями.

Реляционная СУБД

Реляционная СУБД - представление данных в виде таблиц.

im3rwfet5vrf6tre3dtrf6yrt5fer4age.png

Каждая таблица включает информация, которая однозначно идентифицирует строку (первичный ключ или primary key).

Существует также понятие естественного ключа. Это, например, ФИО. Тогда ключ в виде уникального номера - это суррогатный ключ.

Таблица счет содержит 2 внешних ключа (foreign key) - cust_id и product_cd. Эти ключи служат той же цели, что и линии связи в иерархических СУБД.

Нормализация - процесс доработки дизайна БД, гарантирующий, что каждая независимая часть информации находится только в одном месте.

i3etrrgew323r4tr5ymage.png

Классы инструкций SQL

  1. Интрукции схемы SQL - используются для определения структур данных, хранящихся в БД

  2. Инструкции данных SQL - используются для управления структурами данных, ранее определенных с использованием интуркций схемы

  3. Инструкции транзакций SQL - используются для начала, завершения и отката транзакций.

Пример интрукции схемы: создание таблицы с двумя столцами и первичным ключом

[ ]:

%%sql
CREATE TABLE corporation
(corp_id SMALLINT,
name VARCHAR(30),
CONSTRAINT pIncorporation PRIMARY KEY (corp_id)
);

Пример инcтрукции данных: вставляет строку в таблицу

[ ]:

%%sql
INSERT INTO corporation (corp_id, name)
VALUES (27, 'Acme Paper Corporation');

[ ]:

%%sql
SELECT name
FROM corporation
WHERE corp_id = 27;

SQL - непроцедурный язык

Другие процедурные ЯП разбивают скрипт на модули, объекты, функции и процедуры, а также исользуют условную логику (if-then-else) и циклы (for-while). Код обрабатывается компилятором и полученный файл выполняет в точности то, что в нем написано.

  • Процедурный язык определяет как желаемые результаты, так и механизм, или процесс, с помощью которого эти результаты достигаются.

  • Непроцедурный язык также опеределяет желаемые результаты, но процесс, с помощью которого достигаются результаты, остается на усмотрение внешнего агента.

При использовании SQL придется отказатсья от части контроля, потому что он определяет необходимые входные и выходные данные, но способ выполнения интсрукции оставлен на усмотерние оптимизатора.

Задача оптимизатора - просмотреть ваши инструкции SQL и, принимая во внимание то, как устроены таблицы, опеределить самый эффективный способ выполнения.

Если только вы не пишете простой сценарий для управления определенными данными, вам необходимо интегрировать SQL с вашим языком программирования.

Некоторые поставщики баз данных сделали это вместо вас, — например, язык PL/SQL Oracle, язык хранимых процедур MySQL и язык Transact-SQL от Microsoft.

В этих языках инструкции данных SQL являются частью грамматики языка, что позволяет легко интегрировать запросы к базе данных с процедурными командами.

Однако, если вы используете язык, не зависящий от базы данных, такой как Java или Python, вам нужно будет использовать набор инструментов/API для выполнения инструкций SQL из вашего кода.

i2e32re4r4cccfeecfceffcmage.png

3 разных предложения.

[ ]:

%%sql
SELECT /* Одна или несколько вещей */
FROM /* Одно или несколько мест */
WHERE /* Одно или несколько условий */

[ ]:

%%sql
SELECT cust_id, fname
FROM individual
WHERE lname = 'Smith';

[ ]:

%%sql
INSERT INTO product (product_cd, name)
VALUES ('CD', 'Certificate of Depysit')

[ ]:

%%sql
UPDATE product
SET name = 'Certificate of Deposit'
WHERE product_cd = 'CD';

  • возвращено инструкцией select;

  • создано инструкцией insert;

  • изменено инструкцией update;

  • удалено инструкцией delete.

Изучаем SQL (Глава 3). Основы запросов SQL

Каждый раз, когда запрос отправляется на сервер, сервер перед выполнением запроса проверяет следующее. * Есть ли у вас разрешение на выполнение инструкции. * Есть ли у вас разрешение на доступ к нужным данным. * Правильность синтаксиса вашей инструкции.

Если ваша инструкция успешно проходит эти три теста, запрос передается оптимизатору запросов, задача которого — определить наиболее эффективный способ выполнения вашего запроса. Оптимизатор изучает, например, порядок соединения таблиц, указанных в конструкции from, и какие индексы доступны, а затем выбирает план выполнения запроса, который сервер и осуществляет при выполнении вашего запроса.

imsdfdghfgdfdsafsghfgsdfasdage.png

Предложение SELECT определяет, какие из всех возможных столбцов следует включить в результирующий набор запроса.

Все примеры данного учебника выполняются на БД Sekila, строение которой представлено на рисунке ниже

Устройство базы данных Sekila

imEWAFRETRYHDGSAWEQDSEDTRage.png

Таблицы

Предложение FROM определяет таблицы, используемые запросом, наряду со

средствами связывания таблиц вместе.

[3]:

%%sql
SELECT *
FROM language;
#Покажи мне все столбцы и все строки таблицы language

UsageError: Cell magic `%%sql` not found.

[ ]:

%%sql
SELECT language_id, name, last_update
FROM language;

Предложение ``select`` определяет, какие из всех возможных столбцов следует включить в результирующий набор запроса. вы можете включать в предложение select следующее: 1. литералы, например числа или строки; 2. выражения, такие как transaction.amount*-!; 3. вызов встроенных функций, например ROUND ( transaction.amount , 2 ); 4. вызовы пользовательских функций

Таблица - набор связанных строк. Этому ослабленному определению соответствуют четыре разных типа таблиц: * Постоянные таблицы (т .е. созданные с помощью инструкции create table) * Производные таблицы (т .е. строки, возвращаемые подзапросом и хранящиеся в памяти) * Временные таблицы (т .е. изменяемые данные, хранящиеся в памяти) * Виртуальные таблицы (т .е. созданные с помощью инструкции create view).

Каждый из этих типов таблиц может быть включен в предложение from запроса.

Производные таблицы

Подзапрос — это запрос, содержащийся в другом запросе. Подзапросы окружены круглыми скобками и могут встречаться в различных частях инструкции select; однако в предложении from подзапрос служит для создания производной таблицы, видимой из всех других предложений запроса и могущей взаимодействовать с другими таблицами, указанными в предложении from

[ ]:

%%sql
#-- вместо concat() sql_lite имеет ||
select cust.last_name || ', ' || cust.first_name full_name
from (select first_name, last_name, email
from customer
where first_name='JESSIE') cust;

Временные таблицы

Эти таблицы выглядят так же, как и постоянные, но любые данные, добавленные во временную таблицу, в какой-то момент исчезают (обычно в конце транзакции или при закрытии сеанса базы данных). Вот простой пример, как можно временно сохранить актеров, чьи фамилии начинаются с буквы J

Псевдонимы столбцов

[ ]:

%%sql
SELECT language_id,
'COMMON' language_usage,
language_id * 3.1415927 lang_pi_value,
upper(name) language_name
FROM language;

imакыпвераеопгапыфage.png

AS можно опускать

[ ]:

%%sql
SELECT language_id,
'COMMON' AS language_usage,
language_id * 3.1415927 AS lang_pi_value,
upper(name) AS language_name
FROM language;

Удаление дубликатов

Идентификаторы всех актеров, снимавшихся в фильмах, вы бы увидели следующее

[ ]:

%%sql
SELECT actor_id FROM film_actor ORDER BY actor_id;

imaцкуекнеогкрукаge.png

Поскольку некоторые актеры снимались более чем в одном фильме, вы встречаете одни и те же идентификаторы актеров многократно. На самом деле вам, вероятно, нужно множество различных актеров, вместо того чтобы видеть их идентификаторы, повторяющиеся в разных фильмах, в которых они снимались

[4]:

%%sql
SELECT DISTINCT actor_id FROM film_actor ORDER BY actor_id;

UsageError: Cell magic `%%sql` not found.

Предложение FROM

Предложение ``from`` определяет таблицы, используемые запросом, наряду со средствами связывания таблиц вместе.

  • Производные таблицы (генерируемые подзапросами)

  • Подзапрос — это запрос, содержащийся в другом запросе. Подзапросы окружены круглыми скобками и могут встречаться в различных частях инструкции ``select``; однако в предложении from подзапрос служит для создания производной таблицы, видимой из всех других предложений запроса и могущей взаимодействовать с другими таблицами, указанными в предложении ``from``.

В SQL_Lite нет функции concat(). Вместо нее используется ||.

[ ]:

%%sql
SELECT cust.last_name|| ', '||cust.first_name full_name
FROM
(SELECT first_name, last_name, email
FROM customer
WHERE first_name = 'JESSIE'
) cust;

imagFrEGREFRWRe.png

  • В этом примере подзапрос к таблице клиентов возвращает три столбца, а содержащий запрос обращается к двум из этих трех доступных столбцов.

  • Обращение к подзапросу содержащим запросом осуществляется через псевдоним, которым в данном случае является cust.

  • Данные таблицы cust на время запроса сохраняются в памяти, а затем отбрасываются.

Предложение WHERE

Предложение where — это механизм для фильтрации нежелательных строк из вашего результирующего набора.

[ ]:

%%sql
SELECT title
FROM film
WHERE rating = 'G' AND rental_duration >= 7;

imagSGDASFDSe.png

Выбираем только те фильмы, рейтинг которых G и срок аренды больше или равен 7.

Предложение where отфильтровало и отбросило 971 из 1000 строк в таблице film.

[ ]:

%%sql
SELECT title
FROM film
WHERE rating = 'G' OR rental_duration >= 7;

imSFDGFDFSDGSDDHFJage.png

У размер результирующего набора увеличился с 29 до 340 строк

[ ]:

%%sql
SELECT title, rating, rental_duration
FROM film
WHERE (rating = 'G' AND rental_duration >= 7)
OR (rating = 'PG-13' AND rental_duration < 4);

imagSFDFYTSEFDGTFDFDSe.png

Предложения GROUP BY и HAVING

  • group by, которое используется для группировки данных по значениям столбцов.

  • Вы захотели найти всех клиентов, которые брали напрокат 40 или более фильмов. Вместо того чтобы просматривать все 16044 строки в таблице rental, вы можете написать запрос, который дает указание серверу сгруппировать все записи проката по клиентам, подсчитать количество для каждого клиента и вернуть только тех клиентов, у которых это количество имеет значение не меньше 40

  • При использовании предложения group by для создания групп строк можно также использовать предложение having, которое позволяет фильтровать сгруппированные данные так же, как предложение where позволяет фильтровать необработанные данные.

[ ]:

%%sql
SELECT c.first_name, c.last_name, count(*)
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
GROUP BY c.first_name, c.last_name
HAVING count(*) >= 40;

SGDFSDFSDEFDGFSDEFDGF.png

Предложение ORDER BY

  • Предложение order by — это механизм для сортировки вашего результирующего набора с использованием любого необработанного столбца данных или выражения на основе данных столбца.

  • Один вариант предыдущего запроса, который возвращает всех клиентов, бравших фильмы 14 июня 2005 года:

[ ]:

%%sql
SELECT c.first_name, c.last_name,
time(r.rental_date) rental_time
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date)='2005-06-14';

imaASFSDGHTCGUHJGRDFGge.png

  • Для того чтобы результаты находились в алфавитном порядке фамилий клиентов, в предложение order by можно добавить столбец last_name:

[ ]:

%%sql
SELECT c.first_name, c.last_name,
time(r.rental_date) rental_time
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY c.last_name;

imaDAFESGRDTHDGREDASEFRDGge.png

  • В данном примере этого и нет , в больших списках клиентов часто содержатся несколько однофамильцев, поэтому вы можете расширить критерии сортировки, указав в ней и имя человека.

[ ]:

SELECT c.first_name, c.last_name,
time(r.rental_date) rental_time
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date)='2005-06-14'
ORDER BY c.last_name, c.first_name;

Сортировка по возрастанию и убыванию

[ ]:

%%sql
SELECT c.first_name, c.last_name,
time(r.rental_date) rental_time
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) ='2005-06-14'
ORDER BY time(r.rental_date) desc;

imAESFDGTFHYHFGDSFRFDage.png

Сортировка с помощью номера столбцы

[ ]:

%%sql
SELECT c.first_name, c.last_name,
time(r.rental_date) rental_time
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date)='2005-06-14'
ORDER BY 3 desc;

imagDESFDGTRHYRGESFGDRF.png

Упражнения

УПРАЖНЕНИЕ 3.1

Получите идентификатор актера, а также имя и фамилию для всех актеров. Отсортируйте вывод сначала по фамилии, а затем — по имени.

[ ]:

%%sql
SELECT a.actor_id, a.first_name, a.last_name
FROM actor a
ORDER BY a.last_name,a.first_name;

imagуаывпваывааe.png

УПРАЖНЕНИЕ 3.2

Получите идентификатор, имя и фамилию актера для всех актеров, чьи фамилии — ‘ WILLIAMS ‘ или ‘ DAVIS ‘.

[ ]:

%%sql
SELECT a.actor_id, a.first_name, a.last_name
FROM actor a
WHERE a.last_name='WILLIAMS' OR a.last_name='DAVIS';

imуцаупекнекеуаыage.png

УПРАЖНЕНИЕ 3.3

Напишите запрос к таблице rental, который возвращает идентификаторы клиентов, бравших фильмы напрокат 5 июля 2005 года (используйте столбец rental.rental_date; можете также использовать функцию date ( )

[ ]:

%%sql
SELECT  r.rental_id, r.rental_date
FROM rental r
WHERE date(r.rental_date)='2005-07-05';

imagуварпавпыпврпe.png

УПРАЖНЕНИЕ 3.4

Заполните пропущенные места (обозначенные как <#>) в следующем многотабличном запросе, чтобы получить показанные результаты:

[ ]:

%%sql
SELECT c.email  , r.return_date
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date)='2005-06-14'
ORDER BY 2 desc;

imaывпаавыпврвпвыавge.png

Изучаем SQL (Глава 4). Фильтрация

Предложение where может содержать одно или несколько условий, разделенных операторами and и or.

Если несколько условий разделяются только оператором and, то все эти условия должны быть истинными, чтобы строка была включена в результирующий набор.

Однако если все условия в предложении where разделены оператором or, то, чтобы строка была включена в результирующий набор, достаточно, чтобы только одно из условий было истинным.

imaИРОМИРОЬЬИРОЬИРЬИРЬИРОge.png

iДТОЛОМРСАПТРМЛТЮДТage-2.png

imageИРПМАСОМРИБТОЮИ-3.png

Иногда необходимо работать с каждой строкой таблицы. Вот примеры таких ситуаций: * удаление всех данных из таблицы, используемой при подготовке заполнения данными нового хранилища; * изменение всех строк в таблице после добавления нового столбца; * получение всех строк из таблицы очереди сообщений.

В таких случаях в инструкциях SQL предложение where не обязательно, поскольку исключать из рассмотрения какие-либо строки не требуется.

Поэтому все инструкции данных SQL (кроме инструкции insert) включают необязательное предложение where, содержащее одно или несколько условий фильтрации, используемых для ограничения количества строк, на которые действует инструкция SQL.

Кроме того, инструкция select включает предложение having, в которое могут быть включены условия фильтрации, относящиеся к сгруппированным данным.

Условие состоит из одного или нескольких выражений в сочетании с одним или несколькими операторами. Выражение может быть одним из следующего: * Число * Столбец таблицы или представления * Строковый литерал * Встроенная функция, такая как concat() * Подзапрос * Список выражений, такой как (‘Boston’, ’ New York ‘, ‘Chicago’)

Операторы, используемые в условиях, включают: * операторы сравнения, такие как =,!=, <,>,о, like, in и between; * арифметические операторы, такие как +, -,* и /.

Типы условий

Есть много разных способов отфильтровать нежелательные данные. Вы можете искать конкретные значения, наборы значений или диапазоны значений для включения или исключения, можете использовать различные методы поиска по шаблону для поиска частичных совпадений при работе со строковыми данными

Условия равенства (``столбец=выражение``) Такие условия называются условиями равенства, потому что они приравнивают одно выражение к другому.

В следующем запросе используются два условия равенства: одно — в предложении on (условие соединения), а второе — в предложении where (условие фильтрации):

[ ]:

%%sql
SELECT c.email
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date)='2005-06-14';

imaewfgthrhgfesdfge.png

Этот запрос показывает адреса электронной почты каждого клиента, взявшего фильм напрокат 14 июня 2005 года.

Условия неравенства

[5]:

%%sql
SELECT c.email
FROM customer c
INNER JOIN rental r
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) <> '2005-06-14';

UsageError: Cell magic `%%sql` not found.

imgdthfdgsfdgfge.png

Этот запрос возвращает все адреса электронной почты для клиентов, взявших фильм напрокат в любую дату, кроме 14 июня 2005 года. При построении условий неравенства вы можете использовать оператор != или о.

Модификация данных с использованием условий равенства Как удалить те строки из таблицы rental, в которых дата аренды соответствует 2004 году. Вот один из способов решить эту проблему?

[ ]:

%%sql
DELETE FROM rental
WHERE year(rental_date) = 2004;

Вот пример, в котором используются два условия неравенства для удаления любых строк, срок аренды которых не соответствует ни 2005, ни 2006 году:

[ ]:

%%sql
DELETE FROM rental
WHERE year(rental_date) <> 2005 AND year(rental_date) <> 2006;

Условия диапозона

Вы можете создавать условия, которые проверяют, попадает ли выражение в определенный диапазон.

Этот запрос находит все фильмы, взятые напрокат до 25 мая 2005 года.

[6]:

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date < '2005-05-25';

UsageError: Cell magic `%%sql` not found.

imaewrrthgefsetrhyfdgre.png

Эта версия запроса извлекает все фильмы, взятые напрокат 14 или 15 июня 2005 года.

[ ]:

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date <= '2005-06-16'
AND rental_date >= '2005-06-14';

igtshdfgwsrdfhrmage.png

Оператор BETWEEN

СНАЧАЛА НИЖНЯЯ ГРАНИЦА, А ПОТОМ ВЕРХНЯЯ

[ ]:

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date BETWEEN '2005-06-14' AND '2005-06-16';

imsfrdfdgsdage.png

Возвращаются все платежи от 10 до 11,99 долл. Не забудьте убедиться, что сначала указана меньшая сумма.

[ ]:

%%sql
SELECT customer_id, payment_date, amount
FROM payment
WHERE amount BETWEEN 10.0 AND 11.99;

imagfrghnghgfedsfgtre.png

Строковые диапазоны

запрос, который возвращает клиентов, фамилии которых находятся между FA и FR:

[ ]:

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name BETWEEN 'FA' AND 'FR';

imaefrgthytgrfedwefrgge.png

Несмотря на то что есть пять клиентов, чьи фамилии начинаются с FR, они не включены в результаты, поскольку имя наподобие FRANKLIN находится за пределами диапазона.

[ ]:

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name BETWEEN 'FA' AND 'FRB';

imagefrgrfedeftghgf.png

Условия членства

В некоторых случаях вы не будете ограничивать выражение одним значением или диапазоном значений, а будете использовать некоторый конечный набор значений

Вы можете захотеть найти все фильмы с рейтингом G или PG:

[ ]:

%%sql
SELECT title, rating
FROM film
WHERE rating = 'G' OR rating = 'PG';

[ ]:

%%sql
SELECT title, rating
FROM film
WHERE rating IN ('G','PG');

imfgthtgrferthage.png

С помощью оператора in вы можете написать одно условие независимо от того, сколько выражений входит в набор.

Использование подзапросов

если можно считать, что любой фильм, название которого включает строку ‘РЕТ’, будет безопасным для семейного просмотра, то можно выполнить подзапрос к таблице фильмов, чтобы получить все рейтинги, связанные с этими фильмами, а уже затем получить все фильмы, имеющие любой из этих рейтингов:

[ ]:

%%sql
SELECT title, rating
FROM film
WHERE rating IN (SELECT rating FROM film WHERE title LIKE '%PET%');

imagefgbhgbfrergtf.png

Подзапрос возвращает набор ‘ G’ и ‘ PG ‘, а основной запрос проверяет ,находится ли значение столбца rating в наборе, возвращаемом подзапросом.

[ ]:

SELECT title, rating
FROM film
WHERE rating NOT IN ('PG-13','R', 'NC-17');

imagdfhgdfsddgfe.png

Условия соответствия

Можно также использовать встроенную функцию для выделения первой буквы столбца last_name:

!!!!!ВМЕСТО LEFT SQL_LITE ИСПОЛЬЗУЕТ SUBSTR(СТРОКА, СИМВОЛ ПО СЧЕТУ, ДЛИНОЙ)

[ ]:

%%sql
SELECT last_name, first_name
FROM customer
WHERE substr(last_name, 1,1) = 'Q';

imsrdgttfesdgtfage.png

Подстановочные символы

При поиске частичных совпадений строк вас могут заинтересовать: * строки, начинающиеся/заканчивающиеся определенным символом; * строки, начинающиеся/заканчивающиеся некоторой подстрокой; * строки, содержащие определенный символ в любом месте строки; * строки, содержащие подстроку в любом месте строки; * строки определенного формата, независимо от отдельных символов.

imaЫПВЫВЫЯЫАПСВЧАЯВЫВЧПge.png

imSFDGTFHFDFSDFSDFage.png

[ ]:

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name LIKE '_A_T%S';

imafsgtfsfdgge.png

Выражение поиска в предыдущем примере определяет строки, содержащие букву А во второй позиции и Т — в четвертой позиции, за которым следует любое количество символов, заканчивающееся буквой S.

Этот запрос находит всех клиентов, чьи фамилии начинаются с Q или Y.

[ ]:

%sql
SELECT last_name, first_name
FROM customer
WHERE last_name LIKE 'Q%' OR last_name LIKE 'Y%';

imaefsgthrfesfdgrtfge.png

Использование регулярных выражений

Регулярное выражение — это, по сути, поисковое выражение на стероидах

Если же вы никогда их не использовали, возможно, вам стоит обратиться к книге Бен Форта (Ben Forta) Изучаем регулярные выражения (Диалектика, 2019), поскольку это слишком большая тема, чтобы охватить ее в этой книге

[ ]:

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name REGEXP '^[QY]';

null

Значение null — это отсутствие значения; например, до увольнения сотрудника его столбец end_date в таблице employee должен быть null.

Просто не существует значения, которое могло бы быть присвоено столбцу end_date и иметь смысл в этой ситуации. Значение null на самом деле немного шире, так как есть различные случаи его применения.

  • Значение неприменимо Например, столбец идентификатора сотрудника для транзакции, которая выполнена в банкомате.

  • Значение еще неизвестно Например, когда федеральный идентификатор в момент создания строки клиента еще неизвестен.

  • Значение не определено Например, когда создается учетная запись для товара, который еще не был добавлен в базу данных.

  • выражение может быть null, но оно никогда не может быть равным null;

  • два значения null никогда не равны одно другому.

Этот запрос находит все фильмы, взятые напрокат , которые не были возвращены

[ ]:

%%sql
SELECT rental_id, customer_id
FROM rental
WHERE return_date IS NULL;

imaERSRFHGFDGFTJge.png

[ ]:

%%sql
SELECT rental_id, customer_id
FROM rental
WHERE return_date = NULL;

imaEFGTHGRFESDGTRHge.png

[ ]:

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date IS NOT NULL;

imaSGDTHFTGFEDFRTRge.png

Вас попросили найти все взятые напрокат фильмы, которые не вернулись с мая по август 2005 года.

[ ]:

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date NOT BETWEEN '2005-05-01' AND '2005-09-01';

ЫАПЕКРПАЫКПЕКРНmage.png

[ ]:

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date IS NULL
OR return_date NOT BETWEEN '2005-05-01' AND '2005-09-01';

iАУПЕРЕПАУВУАКПЕРmage.png

Результирующий набор теперь входят 62 фильма, которые были возвращены вне окна с мая по август , а также 183 фильма, которые так и не были возвращены, — в общей сложности 245 строки

Упражнения

УПРАЖНЕНИЕ 4.1

Какие из идентификаторов платежей будут возвращены при следующих условиях фильтрации?

/customer_id <> 5 AND (amount > 8 OR date(payment_date) = ‘2005-08-23’)/

Вернуть с идентификатором клтента не равным 5 и (числом более 8 или датой оплаты равной …)

[ ]:

%%sql
select *
from payment
where customer_id <> 5
AND (amount > 8 OR date(payment_date) = '2005-08-23');

imaакперекпкауцувпекраge.png

УПРАЖНЕНИЕ 4.2 Какие из идентификаторов платежей будут возвращены при следующих условиях фильтрации?

/customer_id = 5 AND NOT (amount > 6 OR date(payment_date) = ‘2005-06-19’)/

Выбрать такие, что кастомер айди=5 и не (объем>6 или дата…)

[ ]:

%%sql
select *
from payment
where customer_id = 5
AND NOT (amount > 6 OR date(payment_date) = '2005-06-19');

УПРАЖНЕНИЕ 4.3 Создайте запрос, который извлекает из таблицы payments все строки, в которых сумма равна 1,98, 7,98 или 9,98.

[ ]:

%%sql
select *
from payment
where (amount = 1.98 OR amount = 7.98 OR amount = 9.98);

УПРАЖНЕНИЕ 4.4 Создайте запрос, который находит всех клиентов, в фамилиях которых содержатся буква А во второй позиции и буква W — в любом месте после А.

[ ]:

%%sql
select *
from customer c
where last_name like '_A%W%';

Изучаем SQL (Глава 5). Запросы к нескольким таблицам

Допустим, вы хотите получить имя и фамилию каждого клиента, а также его почтовый адрес. Таким образом, ваш запрос должен будет получить столбцы customer.first_name, customer.last_name и address.address. Но как получить данные из обеих таблиц в одном запросе? Ответ кроется в столбце customer.address_id, содержащем идентификатор записи клиента в таблице address (более формально — столбец customer.address_id является внешним ключом к таблице address). Запрос, который вы вскоре увидите, предписывает серверу использовать столбец customer.address_id в качестве транспорта между таблицами customer и address, что позволяет включать в результирующий набор запроса столбцы из обеих таблиц. Этот тип операции известен как соединение (join).

Декартово произведение

Самый простой способ начать решать поставленную задачу — поместить таблицы customer и address в предложение from запроса и посмотреть, что получится. Вот запрос, который извлекает имена и фамилии клиентов вместе с почтовым адресом, с предложением from, указывающим обе таблицы, разделенные ключевым словом JOIN:

[ ]:



[ ]:



[ ]:



[ ]:

МУСОР

### Технологии и инструменты курса

СУБД: ~~~

  • MySQL - доминирующая РСУБД среди свободного ПО

  • MongoDB - наиболее популярная документоориентированная СУБД (подходит для проектирвоания баз данных с нечеткой или часто меняющейсчя схемой)

  • Redis - наиболее стабильная СУБД типа “ключ-значение” (его преимущество - быстрый доступ к данным)

Проектирование: :sub::sub::sub:~``````

  • MySQL Workbench - популярный и свободный инструмент выполнения запросов и проектирования БД

  • Ваш любимый ORM (наш - Django ORM) - на любом популярном языке есть несколько вариантов

Что такое база данных? :sub::sub::sub:~``````~:sub:~`` Определений много, но есть схожие характеристики: - Хранит данные по правилам (концепция, схема) - Можно управлять данными по правилам - Нужна для удовлетворения информационных потребностей

## Основные понятия баз данных

Что такое база данных? :sub::sub::sub:~``````~:sub:~``

Определений много, но есть схожие характеристики: - Хранит данные по правилам (концепция, схема) - Можно управлять данными по правилам - Нужна для удовлетворения информационных потребностей

Сущность - класс, хранящийся в базе данных, таблица. (Некоторая основа, наделенная смыслом)

Note

Не всякая таблица является сущностью. Некоторые таблицы используются для связывания сущностей.

Объект - экземпляр сущности или класса. Атрибут - некоторое свойство характеризующие сущность, название столбца в таблице.

Кортеж - строка в таблице, набор значений конкретных атрибутов.

Домен - набор допустимых значений атрибута. Например, пол: мужской и женский

Идентификатор - атрибут с уникальным значением для данной таблицы. Как правило - это целое число.

Области применения БД :sub::sub::sub:`~````~~`~

  • Банки, картотеки, любые крупные системы, в которых нужно хранить и изменять данные, а также иметь доступ к ним с разными ролями. (системы, где уже не обойтись xls-файлом) Пример: в базе любого банка определены сущности пользователя, его долгов, операций

  • Веб-сайты - сервер использует БД для удобства управления информацией и взаимодействия с пользователем. Пример: онлайн-магазины оперируют сущностями, корзин, покупателей, скидок, акций

  • Приложения (мобильные и десктопные) используют локальные базы для удобства хранения данных по некоторым правилам.

  • Любой программный продукт, подразумевающий отделение бизнеслогики и уровня хранения данных.

Архитектура СУБД ~:sub::sub::sub:`~`````~

Основные компоненты: - Ядро - процессы, сеть, память, файловая система и т.д. - Диспетчер данных - транзакции, кэш - Диспетчер запросов - парсер запроса, проверяющий валидность запросов; оптимизатор, исполнитель - Набор инструментов для служебных операций - резервное копирование, восстановление, мониторинг

Почему реляционные СУБД популярны? :sub::sub::sub:`~````:sub::sub::sub::sub:`~```````~`~

Преимущества: - Простая схема данных для пользователя. - Логическая и физическая независимость от данных. - Целостность и защищенность данных. - Методологический подход к проектированию. Недостатки: - Относительно низкая скость доступа к данным. - Не универсальное решение для любой предметной области. - Меньшая гибкость при добавлении своих типов данных и операций.

Диалекты: - Oracle (Самый распространенный СУБД) - MySQL (Самый популярный свободный СУБД) - PostgreSQL (Постреляционная СУБД) - MS SQL (СУБД, распространенная в Windows) - SQLite (СУБД, используемая для локальных приложений) - Access (Аналог SQLite от Microsoft)